collapse strings with same id into comma separated list

Question

Based on some googling I came up with this:

drop table #temp
create table #temp
(
    id int,
    name nvarchar(max)
)

insert into #temp (id,name) values (1,'bla1')
insert into #temp (id,name) values (1,'bla2')
insert into #temp (id,name) values (3,'bla3')

;with cte1 as
(
    select id, stuff((select ', ' + CAST(t2.name as nvarchar(max))
         from #temp t2 where t1.id = t2.id
         for xml path('')),1,1,'') name
    from #temp t1
)
select id, name from cte1 group by id, name

Is this the best way to do things? Thanks!

Christian

Answer 1

This approach is slightly better as it just sorts on id rather than id,name

;WITH cte AS
(
SELECT DISTINCT id 
FROM #temp
)
SELECT id, STUFF((SELECT ', ' + CAST(t2.name AS NVARCHAR(MAX))
     FROM #temp t2 WHERE t1.id = t2.id
     FOR XML PATH('')),1,1,'') name
FROM cte t1

If you have a table containing just the distinct id fields already you would probably be better off using that.

The approach you are using only works correctly if the data is guaranteed not to contain any characters such as <, >, &