Get comma-separated value from multiple rows in SQL Server 2005

Question

I have this table

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

I want to request a choice 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 others have said, use COALESCE ...

DECLARE @List VARCHAR(1000)

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

SELECT @List

Answer 3

coalesce

Answer 4

- , , .

.

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

Your query will look like this: "SELECT dbo.Concatenate (Field) FROM Cream"

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