Comma delimited string from row values using COALESCE or FOR_XML

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.