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.
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.
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
