How to group a table, at the same time saving all column values in each group to a single column as comma-separated values

Question

Please see the following SQL code.

Declare @LookUp table (id int,val varchar(12))
insert into @LookUp values (1,'A'),(1,'D'),(1,'X'),(2,'B'),(2,'F')

Declare @summary table (id int,val varchar(2000))
------------------------------
Declare @value varchar(30) 
Declare @start int = 1, @end int = (Select count(Distinct id) from @LookUp)

While @start <= @end
Begin
    Set @value = ''
    Select @value = @value + '|' + val From @LookUp Where id = @start

    Insert into @summary 
    Select @start,Right(@value,Len(@value)-1)

    Set @start = @start + 1
End

Select * From @summary

With the following query, I am grouping based on Id, and making the values with in a group, as comma-separated values.

Input:

enter image description here

Output:

enter image description here

I have done this using a loop, which is not performing very well when it comes to large amount of data.

Can we do this avoiding a loop?

Note: Assume that @lookup.id is continuous.

possible duplicate of [How to use GROUP BY to concatenate strings in SQL Server?](http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) — jpw, Feb 19 '15 at 09:01

score 2 · Answer 1 · answered Feb 19 '15 at 09:03

2

INSERT @summary(id, val)
SELECT
  t.id, 
  STUFF(( 
        select '|' + [val] 
        from @LookUp t1 
        where t1.id = t.id 
        for xml path(''), type 
    ).value('.', 'varchar(max)'), 1, 1, '') [values] 
FROM @LookUp t 
GROUP BY t.id

answered Feb 19 '15 at 09:03

t-clausen.dk

43,517
12
59
92

hello, great Thanks. Just one more thing, If the same logic need to be done in DB2, do we have similar functions. – Jithin Shaji Feb 19 '15 at 09:07
1

@JithinShaji no idea, try to google it. I bet this issue has come up earlier – t-clausen.dk Feb 19 '15 at 09:08

score 2 · Answer 2 · answered Feb 19 '15 at 09:03

Try this..

 Declare @LookUp table (id int,val varchar(12))
   insert into @LookUp values (1,'A'),(1,'D'),(1,'X'),(2,'B'),(2,'F')

    select
        id,
        val = 
            stuff((
                select
                    '| ' + t2.val
                from @LookUp t2
                where
                    t2.id = t1.id
                group by t2.val
                for xml path(''), type).value('.', 'varchar(max)'
            ), 1, 2, '')
    from @LookUp t1
    GROUP BY t1.id

hello, great Thanks. Just one more thing, If the same logic need to be done in DB2, do we have similar functions. — Jithin Shaji, Feb 19 '15 at 09:05

score 2 · Answer 3 · answered Feb 19 '15 at 09:12

Declare @LookUp table (id int,val varchar(12))
insert into @LookUp values (1,'A'),(1,'D'),(1,'X'),(2,'B'),(2,'F');


WITH A AS
(
    SELECT DISTINCT id
    FROM @LookUp
)
SELECT 
    A.id, 
    STUFF
    (
        (
            SELECT  ',' + val 
            FROM    @LookUp B
            WHERE B.id = A.id
            FOR XML PATH('')
        ),
        1, 1, ''
    ) AS val
FROM A

score 2 · Answer 4 · answered Feb 19 '15 at 09:27

Declare @LookUp table (id int,val varchar(12))
insert into @LookUp values (1,'A'),(1,'D'),(1,'X'),(2,'B'),(2,'F')


SELECT DISTINCT a.id, stuff((select '|' + val
                      from @LookUp c 
                      where c.id = a.id 
                      for xml path('')),1,1,'') as vall
FROM @LookUp a

score 2 · Answer 5 · answered Feb 19 '15 at 09:46

I'll add a solution based on a recursive common table expression. With small modifications it should work in DB2 too (you have to change the row_number() function to rownumber() and how the concatenation works in the second list (use || instead of the concat function).

DECLARE @LookUp TABLE (id int, val varchar(12))
INSERT INTO @LookUp VALUES (1,'A'),(1,'D'),(1,'X'),(2,'B'),(2,'F')

;WITH
    list1(rn, id, val) AS
    ( 
      SELECT row_number() OVER (PARTITION BY id ORDER BY id), id, val
      FROM @LookUp
    ),
    list2 (id, val, idx) AS
    ( 
      SELECT id, val, 1
      FROM list1
      WHERE rn = 1
      UNION ALL
      SELECT 
       list2.id, 
      CAST(CONCAT(list2.val, ' | ', list1.val) AS varchar(12)), 
       list2.idx + 1
      FROM list2
     JOIN list1 ON list2.id = list1.id 
      WHERE list2.idx + 1 = list1.rn
    )


SELECT l2.id, l2.val
FROM list2 l2
JOIN (SELECT id, MAX(rn) maxid FROM list1 GROUP BY id) a 
ON l2.id = a.id AND l2.idx = a.maxid
ORDER BY l2.id

This outputs:

id  val
1   A | D | X
2   B | F

What is ur opinion on the performance factor. We expect a lot of data, may be 10 million. — Jithin Shaji, Feb 19 '15 at 11:37
@JithinShaji I wouldn't expect the performance to be good as recursion can be expensive, but give it a try to see how it performs. As opposed to the other solutions this one is portable and doesn't rely on any functions specific to MS SQL Server. — jpw, Feb 19 '15 at 13:12

How to group a table, at the same time saving all column values in each group to a single column as comma-separated values

5 Answers5