SQL: Building a comma separated list from a select clause
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 StringSo, for my table Continent, and my usual SELECT of
SELECT [Name]
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 AmericaThe code is now:
DECLARE @List VARCHAR(1000)SELECT @List = COALESCE(@List + ', ', '') + Name
FROM ContinentSELECT @ListNice and simple. I like!
SELECT [Name]
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 AmericaThe code is now:
DECLARE @List VARCHAR(1000)SELECT @List = COALESCE(@List + ', ', '') + Name
FROM ContinentSELECT @ListNice and simple. I like!
