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?
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments