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

Answer 2

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

Answer 3

As per the comment Lukasz Szozda made on one of the answers here, you should not use your indicated method to aggregate string values in SQL Server, as this is not supported functionality. While this tends to work when no order clause is used (and even if no exception to this tendency has ever been documented), Microsoft does not guarantee that this will work, and there's always a chance it could stop working in the future. SQL is a declarative language; you cannot assume that behaviour that is not explicitly defined as being the correct behaviour for interpreting a given statement will continue working.

Instead, see the examples below, or see this page for a review of valid ways to achieve the same result, and their respective performance: Optimal way to concatenate/aggregate strings

Doing it in a valid way, whichever way you end up using, still has the same considerations as in the other answers here. You either need to exclude NULL values from your result set or be explicit about how you want them to be added to the resulting string.

Further, you should probably use some kind of explicit ordering so that this code is deterministic - it can cause all sorts of problems down the line if code like this can produce a different result when running on the same data, which may happen without an explicit ordering specified.

--Null values treated as empty strings
SET @Foo =
    STUFF /*Stuff is used to remove the seperator from the start of the string*/
        (   (SELECT N','/*separator*/ + ISNULL(RTRIM(t.Field), '' /*Use an emptry string in the place of NULL values*/) /*Thing to List*/
            FROM TABLE t
            ORDER BY t.SomeUniqueColumn ASC /*Make the query deterministic*/
            FOR XML PATH, TYPE).value(N'.[1]',N'varchar(max)')
        ,1
        ,1 /*Length of separator*/
        ,N'');

--Null values excluded from result
SET @Foo =
    STUFF /*Stuff is used to remove the seperator from the start of the string*/
        (   (SELECT N','/*separator*/ + RTRIM(t.Field) /*Thing to List*/
            FROM TABLE t
            WHERE t.Field IS NOT NULL
            ORDER BY t.SomeUniqueColumn ASC /*Make the query deterministic*/
            FOR XML PATH, TYPE).value(N'.[1]',N'varchar(max)')
        ,1
        ,1 /*Length of separator*/
        ,N'');

Answer 4

Don't forget to use LTRIM and RTRIM around @foo (when data type is char/varchar) in the concatenation other it will not give expected results in SQL 2008 R2.