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

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