SQL Server - join rows into comma separated list

Question

Let's suppose I have a temporary table which 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 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 cant find what I am doing wrong. Could someone help with this query? Or point me to a right direction? Thank you.

Answer 1

You are missing the condition inside the sub query.

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