Concatenate sql values to a variable
You'll need to change NULLs
SELECT @foo = @foo + ISNULL(field + ',', '')
FROM TABLE
or remove them
SELECT @foo = @foo + field + ','
FROM TABLE
WHERE field IS NOT NULL
That happens if you have even a SINGLE field in the table that is NULL. In SQL Server, NULL + <any> = NULL
. Either omit them
SELECT @foo = @foo + field + ','
FROM TABLE
WHERE field is not null
Or work around them
SELECT @foo = @foo + isnull(field + ',', '')
FROM TABLE
You can write the whole thing without the leading SET statement which is more common. This query below returns "foo,bar" with no trailing comma
DECLARE @foo NVARCHAR(MAX)
SELECT @foo = isnull(@foo + ',', '') + field
FROM TABLE
WHERE field is not null
PRINT @foo