30

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.

Bogdan Pușcașu
  • 545
  • 2
  • 7
  • 21
  • 3
    Possible duplicate of [How to use GROUP BY to concatenate strings in SQL Server?](https://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) – Ullas Jul 05 '17 at 06:45

5 Answers5

29

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

Shiju Shaji
  • 1,682
  • 17
  • 24
  • Indeed, I was missing the condition. Thanks for the quick answer. – Bogdan Pușcașu Jul 05 '17 at 06:55
  • This solution can cause performance issues, if you cannot upgrade to 2017 its a good choice, but @LukStorms says us SQL Server had [STRING_AGG](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql) – Clystian Oct 23 '18 at 20:16
12

One alternative to using GROUP BY on the 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

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
8

One can also combine a FOR XML with a CROSS APPLY (or an OUTER APPLY) for this.

Example snippet: 

declare @T table (id int, value int);
insert into @T values (1,1),(1,2),(1,3),(2,1),(2,2);

select id, stuff(x.list,1,1,'') as list
from (select distinct id from @T) as t
cross apply (
 select concat(',',t2.value)
 from @T t2
 where t2.id = t.id 
 for xml path('')
) x(list)
order by id;

Result: 

id  list
--  -----
1   1,2,3
2   1,2

And starting from MS Sql Server 2017, STRING_AGG can be used instead.

select id, string_agg(value,',') as list
from Yourtable t
group by id;
LukStorms
  • 28,916
  • 5
  • 31
  • 45
3

Try this :

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
Prabhat G
  • 2,974
  • 1
  • 22
  • 31
0

Simple Solution

SELECT Id, GROUP_CONCAT(Value) as ValueList FROM MyTable GROUP BY Id;

add distinct to values if required

SELECT Id, GROUP_CONCAT(DISTINCT Value) as ValueList FROM MyTable GROUP BY Id;
dsalkamoses
  • 284
  • 1
  • 8