TIP: using GROUP_CONCAT in SQL Server (aka MSSQL) 2005 and above
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 
.