sql - 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

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.