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:
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) select @commaSeperatedNames = @commaSeperatedNames + case
set @commaSeperatedNames = '';
when len(@commaSeperatedNames) > 1 then ', '
else '' end
+ name from category;
The key is to initialize the string var to an empty string so the concatenation works as expected. .