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