Here are a couple of ways to use T-SQL to create a comma separated list of column values in SQL Server. The first uses FOR XML PATH, and returns a csv for each ID in the table – this could be used in a table function.
The second uses COALESCE, and returns a csv for a specified ID – this could be used in a scalar function. Also, since FOR XML PATH is not available prior to SQL Server 2005, the COALESCE version can be used in SQL Server 2000.
First, let’s build a table variable to play with:
DECLARE @tblA TABLE(ID INT, Value INT)
INSERT INTO @tblA VALUES(1000,1)
INSERT INTO @tblA VALUES(1000,2)
INSERT INTO @tblA VALUES(1000,3)
INSERT INTO @tblA VALUES(1001,2)
INSERT INTO @tblA VALUES(1001,3)
INSERT INTO @tblA VALUES(1001,4)
INSERT INTO @tblA VALUES(1001,5)
Here’s the FOR XML PATH version:
SELECT ID,
STUFF(
(
select ‘, ‘+ CAST(value AS VARCHAR)
from @tblA b
WHERE a.ID = b.ID
FOR XML PATH(”)
)
,1,1,”) AS Value
FROM @tblA a
GROUP BY a.ID
Result of query:
ID Value
———————–
1000 1, 2, 3
1001 2, 3, 4, 5
And here’s the COALESCE version:
declare @List varchar(500);
select @List = COALESCE(@List + ‘, ‘, ”) + cast(Value as varchar(5))
from @tblA
where ID = 1000;
select @List;
Results of query:
Value
——-
1, 2, 3
