Create a comma seperated list from a column in a SQL Server table

by cliff 28. September 2008

Problem: you want to create a comma seperated list of values from a column in a database table.  You constraints are that you want to create the comma seperated list at the database level and you can not use a cursor or while loop.

For example, if you have a table called "Category" with a column "Name" that contains the values:

 Name
Breakfast
Lunch
Dinner

and you want to return Breakfast, Lunch, Dinner.

There are many ways to do this and most tend to involve looping or a pivot.  One efficient and elegant solution is to use a simple select statement:

declare @commaSeperatedNames varchar(max)
set @commaSeperatedNames = '';

select @commaSeperatedNames = @commaSeperatedNames + case
        
when len(@commaSeperatedNames) > 1 then ', '
        
else '' end
        
+ name from category;

select @commaSeperatedNames;

The key is to initialize the string var to an empty string so the concatenation works as expected. .

Related posts

Comments

September 28. 2008 23:54

Gravatar

Nice post,
Just wanted to add that, if you want to generate more than one string (for example, a string for each category etc) and if you are on SQL Server 2005 or above, FOR XML PATH can be used. I have presented an example here: http://www.sqlserverandxml.com/2008/08/how-to-generate-delimited-string-using.html

Regards
Jacob

Jacob Sebastian in

November 28. 2008 01:23

Gravatar

this post helped me very much...

Mohamed Ysir.P in

Comments are closed

Cliff Gray's Info

Cliff Gray
Developer/Founder GrayTechnology.com.

E-mail me Send mail

Authors

Calendar

<<  September 2019  >>
MoTuWeThFrSaSu
2627282930311
2345678
9101112131415
16171819202122
23242526272829
30123456

View posts in large calendar

Blogroll

Download BlogEngine.NET

Download at CodePlex

Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2019

Subscribe