Group and values from table

Question

I have a table:

Col1   Col2
---    ---
Bar    Val1
Bar    Val2
Bar    Val3
Foo    Val4
Foo    Val5
Foo    Val6

I need to write a query that outputs

Col1         Col2
---    ----------------
Bar    Val1, Val2, Val3
Foo    Val4, Val5, Val6

I need to write it as a single query, so couldn't use COALESCE() for concatenating, as it would require using variable and loop.

My other solution was to use recursive CTE. However, I need to concatenate values for both 'Bar' and 'Foo'. I thought of CROSS APPLY but don't know is it possible to achieve this result with using CROSS APPLY and recursive CTE.

Any suggestions?

Google: "SQL Server aggregate string concatenation". This is a typical problem and the solution usually uses "for xml path". — Gordon Linoff, Nov 19 '15 at 23:18
Possible duplicate of [Simulating group\_concat MySQL function in SQL Server?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server) — Felix Pamittan, Nov 19 '15 at 23:18
only for suggestion :: if you are going to show the result in UI, I mean you are using C#/JAVA etc. for front-end, better to do the same using PLs rather than in database level. — user4221591, Nov 19 '15 at 23:51

score 1 · Accepted Answer · answered Nov 19 '15 at 23:34

1

select distinct 
    Col1,
    (
     select STUFF((select ',' + col2 
     from yourtable b 
     where b.col1 = a.col1 for xml path('')),1,1,'')
     ) as Col2
from yourtable a

answered Nov 19 '15 at 23:34

Mat Richardson

3,576
4
31
56

score 0 · Answer 2 · answered Nov 19 '15 at 23:37

Try it like this:

DECLARE @tbl TABLE(Col1 VARCHAR(100),Col2 VARCHAR(100));
INSERT INTO @tbl VALUES
('Bar','Val1')
,('Bar','Val2')
,('Bar','Val3')
,('Foo','Val4')
,('Foo','Val5')
,('Foo','Val6');

SELECT DISTINCT Col1,Concatenated.Col2
FROM @tbl AS tbl
CROSS APPLY(
    SELECT STUFF
    (
        (
            SELECT ', ' + Col2 
            FROM @tbl AS InnerTbl
            WHERE InnerTbl.Col1=tbl.Col1
            FOR XML PATH('')
        )
    ,1,2,'')
) AS Concatenated(Col2)

/* Result
Col1    Col2
Bar Val1, Val2, Val3
Foo Val4, Val5, Val6
*/

Group and values from table

2 Answers2