Concatenate sql values to a variable

Question

On a SQL Server 2008 I'm trying to get a comma separated list of all selected values into a variable.

SELECT field
FROM table

returns:

+-------+
| field |
+-------+
| foo   |
+-------+
| bar   |
+-------+

I'd like to get: "foo, bar, "

I tried:

DECLARE @foo NVARCHAR(MAX)
SET @foo = ''

SELECT @foo = @foo + field + ','
FROM TABLE

PRINT @foo

Which returns nothing. What am I doing wrong?

Answer 1

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