SQL comma separated list
Build a comma separated string from the values of a table column with a single COALESCE query.
1 min read#SQL
Sometimes you need a comma separated list of the values in a column, for example to paste into an IN (...) clause. A variable and COALESCE do it without a cursor.
sql
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr + ''',''', '') + ColumnName
FROM TableName
SELECT @listStr
The separator is ',' including the quotes, so the result can be wrapped in one more pair of quotes and used directly in a filter.
Example
If ColumnName holds A, B and C, the result is:
text
A','B','C