Building a comma separated list?

Question

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?

Answer 1

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)

Answer 2

Not sure if this applies exactly to what you're looking for, but I found this right at the same time I found your questions. I use the second solution with FOR XML PATH, which Matt Hamilton mentioned above. It's worked great for me.

Concatenating Rows - By Carl P. Anderson, 2009/10/14

http://www.sqlservercentral.com/articles/T-SQL/67973/

Answer 3

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.

Answer 4

declare     @output varchar(max)

select      @output = coalesce
                      ( 
                          @output + ', ' + convert(varchar(max),cat_id), 
                          convert(varchar(max),cat_id)
                      )
from        yourTableHere

print       @output

Answer 5

What you are doing wrong is that @output is not null from start, but an empty string. Set @output to null before the loop (or if it's not used since it's declared just don't assign an empty string to it).

Answer 6

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