How to combine multiple rows in MSSQL [duplicate]

Question

Possible Duplicates:
Simulating group_concat MySQL function in MS SQL Server 2005?
Concat groups in SQL Server

I am trying to write an SQL query that will combine rows together. I need it to group together an unspecified number of rows by ID# but concatenate their addresses let's say into one cell.

Say we have

ID, Address
p1 a1
p1 a2
p1 a3
p2 a4
p2 a5

I want to get

ID, Address
p1 a1,a2,a3
p2 a4,a5

The number of addresses per ID can vary. Some IDs have 1, others can have 50.

Any help would be appreciated. Thanks in advance!

Answer 1

The Coalesce function can be used to produce that result. Coalesce returns the first non null value in a set. If you use it in the following manner it will recursivly return each value in order until it nulls out and return it as a comma delimited string. You can use any other delimiter if you want.

DECLARE @pAddr VarChar(MAX)
SELECT @pAddr = COALESCE(@pAddr + ', ', '') + [Address]
FROM AddressTable
WHERE AddressTable.Key = @pKey

You could put this code into a UDF, and then in a view simply call that UDF passing the key.

Answer 2

Select T1.Id
    , Stuff(
        (
        Select ', ' + T2.Address
        From MyTable As T2
        Where T2.Id = T1.Id
        Order By T2.Address
        For Xml Path(''), type
        ).value('.', 'nvarchar(max)'), 1, 2, '') As Address
From MyTable As T1
Group By T1.Id