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

Yeah it looks like most of the questions you asked have been answered satisfactory, so just select an answer.. — Colin, Sep 28 '09 at 17:56

score 6 · Answer 1 · answered Sep 28 '09 at 10:28

6

With sysobjects this worked:

DECLARE @List varchar(2000)

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

SELECT @List As 'List'

answered Sep 28 '09 at 10:28

Arthur

brilliant, simply brilliant. I have always went down the aggregate UDF path before but this solution relies on sequential evaluation of the variable expressions! – Andriy Volkov Sep 28 '09 at 18:05

score 4 · Answer 2 · answered Sep 28 '09 at 17:54

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

answered Sep 28 '09 at 17:54

Jane

score 3 · Answer 3 · answered Sep 28 '09 at 10:26

3

You can use coalesce

answered Sep 28 '09 at 10:26

Adriaan Stander

score 0 · Answer 4 · answered Sep 28 '09 at 10:59

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.

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

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