Building a comma separated list?
Solution 1
Are you on SQL 2005? With props to Rob Farley who showed me this just recently:
SELECT stuff((
SELECT ', ' + cast(cat_id as varchar(max))
FROM categories
FOR XML PATH('')
), 1, 2, '');
The inside query (with FOR XML PATH('')
) selects a comma-separated list of category IDs, with a leading ", ". The outside query uses the stuff function to remove the leading comma and space.
I don't have an SQL instance handy to test this, so it's from memory. You may have to play with the stuff parameters etc to get it to work exactly how you want.
Solution 2
COALESCE Returns the first nonnull expression among its arguments
First argument @output + ', '
is never null (unless you initialize @output
as null AND set CONCAT_NULL_YIELDS_NULL
to ON
), so it's always returned.
Solution 3
check @output
value just before the execution of this query, I think it's not equal to NULL
but to '' (empty string)
EDIT: (after the @auth edited the question)
now I'm sure it's '',
you have to initialize it to NULL
to do it independently of CONCAT_NULL_YIELDS_NULL
, use the old CASE WHEN
:
select @output = NULL
select @output = CASE WHEN @output IS NULL THEN '' ELSE @output+', ' END + value
Solution 4
And sometimes...
you have to answer your own question
declare @output varchar(max)
select @output = case when (@output is null) then '' else ', ' END + convert(varchar(max),cat_id)
Solution 5
declare @output varchar(max)
select @output = coalesce
(
@output + ', ' + convert(varchar(max),cat_id),
convert(varchar(max),cat_id)
)
from yourTableHere
print @output
Related videos on Youtube
Comments
-
pablo almost 2 years
I'm tryin to use SQL to build a comma separated list of cat_id's
the code is:
declare @output varchar(max) set @output = null; select @output = COALESCE(@output + ', ', '') + convert(varchar(max),cat_id)
edit: changed '' to null, STILL same. but the output im getting is like so:
, 66 , 23
the leading comma should not be there. What have i missed?
-
The Chairman over 14 yearseven then
CONCAT_NULL_YIELDS_NULL
needs to be set toON
-
Andomar over 14 years+1 for the pointer to the ANSI property. Use "select sessionproperty('CONCAT_NULL_YIELDS_NULL')" to see its current value; it is 1 by default, but if it's 0, it would cause the poster's COALESCE to fail.
-
Ryanman over 9 yearsI wanted to append to this answer - for a lot of STUFF statements you want the parameters to be 1 and 1, not 1 and 2 to create a truly comma-seperated list. Matt's answer uses 2 because he has a space after each comma. For more info, check out the MSDN article: msdn.microsoft.com/en-us/library/ms188043.aspx