tsql aggregate string for group by

Question

I have two tables:

Names(id, name) Addresses(id, name_id, address)

I want to write query that return me: name, address list (address1, address2, adress3, ..)

Something like:

Select A.name, B.list_of_addresses
From Names A
    Inner Join (Select name_id, /*list_of_addresses with comma between them*/
                From Addresses
                Group By name_id)  B ON A.id=B.name_id

You can use a common table expression to concat the strings, then the rest of the query works. — Ken Downs, Jan 24 '11 at 21:46
@Ken Downs: Can you giva an example? I don't really understand. @OMG Ponies: I am using SQL Server 2005. — Naor, Jan 24 '11 at 22:23

score 6 · Answer 1 · answered Jan 24 '11 at 21:46

6

You can use For XML as a trick to achieve that from SQL Server 2005 onwards.

Select
    A.name,
    stuff((
        select ',' + B.address
        from Addresses B
        WHERE A.id=B.name_id
        for xml path('')),1,1,'')
From Names A

It works well if you don't already have commas in the address, but even if it did, since your request is to put commas between them.. this is probably just as 'right'.

answered Jan 24 '11 at 21:46

RichardTheKiwi

105,798
26
196
262

What does for xml path('') does? – Naor Jan 24 '11 at 22:27
1

FOR XML returns the data as XML. Path(x) gives a tag name for each node. Path('') gives an empty name, so the node values `(','+B.address)` end up as plain text instead in a single row. STUFF is a function to remove the first ',' from the list. – RichardTheKiwi Jan 24 '11 at 22:29
Do you know how can I grub the result in c#? does the result type of string? xml? – Naor Jan 24 '11 at 22:33
The 2nd column is a NVARCHAR(MAX) column. – RichardTheKiwi Jan 24 '11 at 22:35

tsql aggregate string for group by

1 Answers1

Linked