Creating CSV string in TSQL

Question

I have a table with the following values:

NAME      Value1       Value2
--------------------------------
N1          1           10
N1          2           11
N2          3           12
N2          4           13
N2          5           14

and I want to create the following result:

NAME      Value1        Value2_CSV
-----------------------------------
N1          1           10,11
N1          2           10,11
N2          3           12,13,14
N2          4           12,13,14
N2          5           12,13,14

Could anyone help please? Thanks in advance

score 3 · Accepted Answer · answered Nov 24 '14 at 13:47

This is a variation on the aggregate string concatenation -- but without the aggregation.

I would write this as:

select t.name, t.value1,
       stuff((select ',' + cast(t2.value2 as varchar(8000))
              from table t2
              where t2.name = t.name
              order by t2.value1
              for xml path ('')
             ), 1, 1, '') as value2_csv
from table t;

score 0 · Answer 2 · answered Nov 24 '14 at 13:45

This query will be helpful.

DECLARE @Table AS TABLE
(
    NAME VARCHAR(100),
    Value1 Int,
    Value2 Int
)

INSERT INTO @Table
SELECT 'N1', 1 , 10 UNION
SELECT 'N1', 2 , 11 UNION
SELECT 'N2', 3 , 12 UNION
SELECT 'N2', 4 , 13 UNION
SELECT 'N2', 5 , 14 


SELECT NAME, Value1, Value2_CSV = 
    STUFF((SELECT ',' + CAST(B.Value2 as VARCHAR(MAX)) 
           FROM @Table AS B WHERE B.NAME = A.NAME
           GROUP BY B.Value2 for XML PATH (''), TYPE)
            .value('.', 'varchar(max)') ,1,1,'')
FROm @Table AS A

Creating CSV string in TSQL

2 Answers2