How to create output as CSV using FOR XML and multiple rows

For example consider source data as follows

Source data:

Group	Value
1	1
1	2
1	3
1	4
2	3
2	2
2	1
3	1
3	2

Result required:

Group	Value
1	1,2,3,4
2	3,2,1
3	1,2

FOR XML only gets you so far, well – 1 row to be exact, so how do we break it out for the multiple rows per group?

The trick is to use a sub-query on the SELECT and wrap your FOR XML logic into that…

Source code:

--Declaring temporary table variable

declare @tb table(agrp int,aval int)

--Inserting data(s) into temporary table

insert @tb values(1, 1)

insert @tb values(1, 2)

insert @tb values(1, 3)

insert @tb values(1, 4)

insert @tb values(2, 3)

insert @tb values(2, 2)

insert @tb values(2, 1)

insert @tb values(3, 1)

insert @tb values(3, 2)

 

--Query to print the desired output

SELECT r.agrp,

       collapsed = LEFT(r.collapsed, LEN(r.collapsed) - 1)

FROM   (

           SELECT a.*,

                  collapsed = (

                      SELECT CAST(aval AS VARCHAR(MAX)) + ',' AS [text()]

                      FROM   @tb b

                      WHERE  b.agrp = a.agrp

                             FOR XML PATH('')

                  )

           FROM   (

                      SELECT DISTINCT agrp

                      FROM   @tb

                  ) AS a

       ) AS r

Output screenshot: