Aggregate Row String Concatenation in MS Access

Posted By: ktr | May 7th, 2008 @ 10:43 AM
page 1 of 1
Comments: 3 | Views: 2741
#May 7th, 2008 @ 10:43 AM
ktr
ktr
two sides to everything
Does anyone know how to perform an aggregate string concatenation in Microsoft Access without using a VBA module? I want pure SQL.

Across columns of a table is easy. Ex:

People

Id  Name  Dob
1   John  1/2/1975
2   Bob   1/2/1975
3   Tim   5/7/1955

MS Access SQL:

SELECT p.Id, (p.Name & ' ' & p.Dob) AS Person
FROM People p

I have no idea how I would go about concatenating rows. Does anyone have experience with that?

Ex: Get a comma-delimited list of people with the same birthday

Results should look like this:

Dob       People
1/2/1975  John, Bob
5/7/1955  Tim

Thanks for the help.
#May 7th, 2008 @ 11:35 AM
W3bbo
W3bbo
The Master of Baiters
As far as I know, in Access, it's impossible, since you can't batch commands like you can with T-SQL.
#May 7th, 2008 @ 1:38 PM
evildictaitor
evildictaitor
if( !succeed( try() ) ) { while(true) try(); }
Surely you can just get an enumerable collection out of SQL in your data and then join them?

SQL shouldn't allow you to aggregate rows because it means all of the general optimisations that are possible (through select, where etc) go out the window - since you're down to a single (typically gargantuan) element.
#May 7th, 2008 @ 1:46 PM
figuerres
figuerres
???
ktr wrote:
Does anyone know how to perform an aggregate string concatenation in Microsoft Access without using a VBA module? I want pure SQL.

Across columns of a table is easy. Ex:

People

Id  Name  Dob
1   John  1/2/1975
2   Bob   1/2/1975
3   Tim   5/7/1955

MS Access SQL:

SELECT p.Id, (p.Name & ' ' & p.Dob) AS Person
FROM People p

I have no idea how I would go about concatenating rows. Does anyone have experience with that?

Ex: Get a comma-delimited list of people with the same birthday

Results should look like this:

Dob       People
1/2/1975  John, Bob
5/7/1955  Tim

Thanks for the help.


in "pure" sql you can't really do that ....
with MSSQL there is a trick that can kind of do that but even then it just takes N rows and makes 1 row.

in MSSQL server you would need to use a MS / TSQL extension
or use a stored proc with a cursor.

I would just do it with a small bit of VBA code ....
order on the DOB and build a row till DOB <> LastDOB
then start the next set/row

just a while loop on a recordset till you hit the end.
not hard.
page 1 of 1
Comments: 3 | Views: 2741
Microsoft Communities