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

You can use coalesce

Answer 2

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 3

The best way to do this, is to create an aggregate user defined function and register it on the database.

Try here for an example.

http://msdn.microsoft.com/en-us/library/ms165055.aspx

Your query would look like "SELECT dbo.Concatenate(Field) FROM Cream"

You will be returned what you expect "a,b,c,d..." ect.

Answer 4

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