Code Wizards’ Blog

Ever had to move from MySQL and tried to use a non-standard command like GROUP_CONCAT so you can get a summary of information from a subselect?
Well, the ordinary way is to create a user defined function (udf) or a summary table.  But, say you just really want to get a GROUP_CONCAT from Microsoft SQL Server 2005 (or above) and you don’t want any hassle?

Try the following approach put together by SQL Garbage Collector - I’ve used it here to grab a CSV of Ids linked to the item

SELECT item.*,

/* then do a subselect using the stuff attribute and a blank xml path to retrieve a CSV formatted list of values */

stuff( ( select ','+ cast(attr.attributeid as varchar(50))

from attribute attr

where item.titleid = attr.titleid

for xml path('') ),1,1,'') as attributeids

FROM itemcollector item

WHERE item.itemid = 1;

and that’s it - it’s not as simple or elegant as GROUP_CONCAT but it’s a useful trick to know all the same - your days of fretting over SQL2005 doing GROUP_CONCAT are over .