Geek asks and answers

Collapse lines with the same identifier in a comma-separated list

Based on some kind of googling, I came up with the following:

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 something? Thank!

Christian

+3
source share
1 answer

This approach is slightly better as it just sorts id, notid,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 only individual fields id, you probably would be better off using this.

The approach that you use, only works properly if the data is guaranteed not to contain any characters, such as <, >,&

+2
source

More articles:


All Articles