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 <
, >
, &
Comments
Post a Comment