9/29/2010 11:39:19 AM
This is how to get a comma delimited string from row values using COALESCE or FOR_XML
declare @asd varchar(2000); select @asd = COALESCE(@asd + ', ', '') + '''' + convert(varchar(50),c1.guid) + '''' from clicks c1 inner join clicks c2 on c1.guid = c2.guid group by c1.guid having count(*) > 1;
But the next on is far better, using no variable, taking sort into consideration and can be used in sub queries.
select id, stuff(( select ',' + t.[name] from t1 t where t.id = t1.id order by t.[name] for xml path('') ),1,1,'') as name_csv from t1 group by id;
Thanks to this article by Rob Farley.