Get comma separated value from multiple row in sql

问题:

i have this table

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

want out put in select query like this

cream

CHOCALATE,GREEN,TEST

回答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'

回答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

回答3:

You can use coalesce

回答4:

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.