sql server - collapse strings with same id into comma separated list -

based on googling came this:

drop table #temp create table #temp (     id int,     name nvarchar(max) )  insert #temp (id,name) values (1,'bla1') insert #temp (id,name) values (1,'bla2') insert #temp (id,name) values (3,'bla3')  ;with cte1 (     select id, stuff((select ', ' + cast(t2.name nvarchar(max))          #temp t2 t1.id = t2.id          xml path('')),1,1,'') name     #temp t1 ) select id, name cte1 group id, name 

is best way things? thanks!

christian

this approach better sorts on id rather id,name

;with cte ( select distinct id  #temp ) select id, stuff((select ', ' + cast(t2.name nvarchar(max))      #temp t2 t1.id = t2.id      xml path('')),1,1,'') name cte t1 

if have table containing distinct id fields better off using that.

the approach using works correctly if data guaranteed not contain characters such <, >, &