Construct a comma delimited list from table column with SQL

How many times have you had to create a comma delimited list from a table column? Do you ever remember how to do it? I don’t. Here is a little snippet I find useful:

-- this will be a comma delimited list of location ids
DECLARE @LocationIds Varchar(Max)
-- set it to an empty string to start with
SET @LocationIds = ''

--add commas 
SELECT @LocationIds = COALESCE(@LocationIds + ',','') + CAST(Location_ID AS VARCHAR) FROM Locations_Table
--get rid of the comma at the front of the string
SELECT SUBSTRING(@LocationIds, 2, Len(@LocationIds))

Result: 1234,3445,6778,6789,…

comments powered by Disqus