Query Problem Column to string

Question

Query Problem Column to string

Martin D. Cymerman

SSCrazy

Points: 2079
More actions
May 9, 2007 at 6:42 am

#370989

How can a write a query such that the output of a column is like
value1,value2,value3,value4
rather that the normal output of
value1
value2
value3
value4
Thanks
CY

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Many different was to do this. It is called a pivot table and there have been many responses on this. But for your answer the question is how do you identify the records are related or do you want all rows to be concatinated together?

Ian Yates SSCoach Points: 19738 More actions · Answer 2

It's always "yukky" to return data like that, but if you must, then you can do something like

declare @X varchar(8000)

set @X = ''

select @X = @X + ',' + myCol

from myTable

order by myOrderingColumn

select @X

There are many other ways to do it. Apparently it is not documented either although that's a claim I've never checked (others have made the claim). I can tell you that it appears to work in SQL 2000 and 2005.

Ian Yates SSCoach Points: 19738 More actions · Answer 3

and I realise that my code includes an extra comma Fix as needed

Martin D. Cymerman SSCrazy Points: 2079 More actions · Answer 4

I would just one column of all the values. Example Select EmailAddress from EMP.

Instead of

EmailAddress

------------

Name@someplace.com

I need

Name@someplace.com,Name@someplace.com

I have searched but just not have found anything yet...

Thanks,

CY

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Why do you need the result returned as a comma seperated list?

Martin D. Cymerman SSCrazy Points: 2079 More actions · Answer 6

Martin D. Cymerman

SSCrazy

Points: 2079

May 9, 2007 at 8:23 am

#705237

Thanks Ian

Works great!

CY

Antares686 SSC Guru Points: 125444 More actions · Answer 7

Then Ian's solution should be close to what you are after.