SQL Server - combining rows into a comma separated list

Question

Suppose I have a temporary table that looks like this:

+----+------+ | Id | Value| +----+------+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 1 | | 2 | 2 | +----+------+ 

And I want my table to be like this:

  +----+----------+ | Id | ValueList| +----+----------+ | 1 | 1,2,3 | | 2 | 1,2 | +----+----------+ 

So basically I need to group my values ​​as a comma-separated list. I have already tried the following:

 SELECT Id, STUFF((SELECT ',' + CAST(VALUE AS varchar) FROM @MyTable FOR XML PATH('')), 1 ,1, '') AS ValueList FROM @MyTable GROUP BY Id 

But I get something like:

  +----+---------------------+ | Id | ValueList | +----+---------------------+ | 1 | 1,1,1,1,1,1,... | +----+---------------------+ 

I cannot find what I am doing wrong. Can anyone help with this question? Or point me in the right direction? Thanks.

Answer 1

You are missing a condition inside an auxiliary request.

 SELECT t2.Id, STUFF((SELECT ',' + CAST(VALUE AS varchar) FROM @MyTable t1 where t1.Id =t2.ID FOR XML PATH('')), 1 ,1, '') AS ValueList FROM @MyTable t2 GROUP BY t2.Id 

Demo

Answer 2

An alternative to using GROUP BY in Id would be to use select distinct:

 SELECT DISTINCT Id, STUFF((SELECT ',' + CAST(t2.VALUE AS varchar) FROM @MyTable t2 WHERE t2.Id = t1.Id FOR XML PATH('')), 1 ,1, '') AS ValueList FROM @MyTable t1 

Demo

Answer 3

Try the following:

 create table #t(id int, value int) insert into #t values (1,1), (1,2), (1,3), (2,1), (2,2) SELECT t2.Id, STUFF((SELECT ',' + CAST(VALUE AS varchar) FROM #t t1 where t1.Id =t2.ID FOR XML PATH('')), 1 ,1, '') AS list FROM #t t2 GROUP BY t2.Id 

output:

 Id list --- ------- 1 1,2,3 2 1,2