tsql aggregate string for group by
I have two tables:
Addresses(id, name_id, address)
I want to write query that return me:
name, address list (address1, address2, adress3, ..)
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
One Solution collect form web for “tsql aggregate string for group by”
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’.