Create CSV string in TSQL
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
Can anyone please help? thanks in advance
+3
source to share
2 answers
This is a variation on aggregate string concatenation, but no aggregation.
I would write it like:
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;
+3
source to share
This request 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
0
source to share