CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sqlgroup-concat
Naor
Naor

Reputation: 24103

tsql aggregate string for group by

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

Upvotes: 1

Views: 5169

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107826

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'.

Upvotes: 6

Related Questions