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?
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
@var in each row. After ordering occurs on these "transient rows", some historical version of the @variable state gets imprinted as the final value of the assignment. Moral of the story is - don't use ORDER BY, or use FOR XML.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.
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'');
