A colleague asked me today about generating a comma separated list of values based on the results of a SELECT statement. A quick google later and I found Using COALESCE to Build Comma-Delimited String

So, for my table Continent, and my usual SELECT of
FROM Continent

with just a variable declaration and a use of COALESCE and hey presto, we get our required result of Africa, Antarctica, Asia, Australia and Oceania, Europe, North America, South America

The code is now:

SELECT @List = COALESCE(@List + ', ', '') + Name
FROM Continent


Nice and simple. I like!