Building A Comma Separated List?

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 + ', ',

Solution 1:

Are you on SQL 2005? With props to Rob Farley who showed me this just recently:

SELECT stuff((
    SELECT', '+cast(cat_id asvarchar(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:

And sometimes...

you have to answer your own question

declare@outputvarchar(max)
select@output=casewhen (@outputisnull) then''else', 'END+convert(varchar(max),cat_id)

Solution 4:

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=NULLselect@output=CASEWHEN@outputISNULLTHEN''ELSE@output+', 'END+value

Solution 5:

declare@outputvarchar(max)

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

print       @output