T-SQL functions to create comma separated list

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s