Group multiple rows into single row

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris McFarling - 08 Jul 2008 23:50 GMT

Take the following table...

TABLE_A

id value
----------
10 dog
10 cat
10 moose
10 squirrel
11 dog
11 cat
11 moose
11 squirrel
12 dog
12 cat
13 dog
13 cat
13 moose
13 squirrel

I'm trying to construct a query that will return the following result:
id value
10 dog,cat,moose,squirrel
11 dog,cat,moose,squirrel
12 dog,cat
13 dog,cat,moose,squirrel

Anyone have a quick solution?

Reply to this Message

Plamen Ratchev - 08 Jul 2008 23:59 GMT

On SQL Server 2005 you can use FOR XML PATH with empty element to
concatenate the values:

SELECT DISTINCT
id,
STUFF(value_list, 1, 1, '') AS list
FROM TableA AS A
CROSS APPLY (SELECT ',' + value
FROM TableA AS B
WHERE B.id = A.id
FOR XML PATH('')) AS T(value_list);

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

Chris McFarling - 09 Jul 2008 02:05 GMT

I should have specified, I'm on SQL Server 2000.

> On SQL Server 2005 you can use FOR XML PATH with empty element to
> concatenate the values:
[quoted text clipped - 12 lines]
> Plamen Ratchev
> http://www.SQLStudio.com

Reply to this Message

Plamen Ratchev - 09 Jul 2008 02:43 GMT

Take a look at the following article, it has a few methods for SQL Server
2000:

http://www.projectdmx.com/tsql/rowconcatenate.aspx

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message