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