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

Cliff Gray's Info

Cliff Gray
Developer/Founder GrayTechnology.com.

E-mail me Send mail

Authors

Calendar

<<  April 2018  >>
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 2018

Subscribe