Create comma delimited list in SQL Server

Many a times need arises to create comma delimited list in SQL Server. This can be done using the DOR XML PATH feature of SQL Server. The FOR XML PATH generates a xml when used with select statement. An example of same is shown below.

DECLARE @tb TABLE(sno int identity,col1 varchar(10))

INSERT INTO @tb VALUES('a'),('b'),('c'),('d')

SELECT ',' + col1 from @tb 
			for xml path('')

The above query creates a table variable and does a simple select on it with FOR XML PATH. The delimiter in our case is a “,”. The output of above query is shown below.

1_create comma delimited list in sql

We get a comma separated list, however we still need to get rid of the first comma before “a”. This can be done using the STUFF function as shown below.

   
DECLARE @tb TABLE(sno int identity,
	col1 varchar(10))

INSERT INTO @tb VALUES('a'),('b'),('c'),('d')

SELECT 
	STUFF(
			(SELECT ',' + col1 from @tb 
			for xml path('')),
			1,
			1,
			''
		 ) AS Comma_separated_list

The above query removes the annoying “,” and returns below result.

2_create comma delimited list in sql

Do share if you have any other interesting way of solving this issue.

 
Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

   

Leave a Reply

Your email address will not be published.