CODEWITHSUNDEEP
sqlsql-servert-sqlconcatenationsql-server-2014
LynnXe
LynnXe

Reputation: 57

Group and values from table

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?

Upvotes: 1

Views: 40

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67321

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
*/

Upvotes: 0

Mat Richardson
Mat Richardson

Reputation: 3606

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

Upvotes: 1

Related Questions