Get comma separated value from multiple row in sql server 2005

Question

i have this table

Cream
----------
CHOCALATE
GREEN
TEST

want out put in select query like this

cream

CHOCALATE,GREEN,TEST

Answer 1

With sysobjects this worked:

DECLARE @List varchar(2000)

SELECT @List = COALESCE(@List + ',', '') + Cast(name As varchar(50))
FROM sys.sysobjects

SELECT @List As 'List'

Answer 2

I found a useful resource here when I needed to do this, but as the others said, use COALESCE...

DECLARE @List VARCHAR(1000)

SELECT @List = COALESCE(@List + ', ', '') + Name
FROM Cream

SELECT @List