How to get the result of a query which has several lines as a result in one single line separated by one separator?

Question

I need to get the value from one query as a single row, separated by one separator, chosen but me, but it's a little tricky.

So, I have:

select employer from employers

I will return:

(line1)    Anne
(line2)    Sue
(line3)    Villy
(line4)    John
(nth line) Marry

I need to have the result as this:

'Anne','Sue','Villy','John','Marry'

Or at least:

 Anne^Sue^Villy^John^Marry

Any hints? Thanks,

score 2 · Accepted Answer · answered Apr 08 '13 at 13:52

2

declare @s varchar(max) = ''

select @s = case 
    when @s <> '' then  
        @s + ',''' + employer + ''''
    else 
        @s + '''' + employer + '''' end 
from employers

select @s

answered Apr 08 '13 at 13:52

Kevin Collins

1,453
1
10
16

Thanks a million. Saved me from cursors! It worked like a charm. Have a great day! – BogdanM Apr 08 '13 at 13:57

score 2 · Answer 2 · answered Apr 08 '13 at 13:55

2

SELECT STUFF((SELECT DISTINCT  ', ' + Employer AS [text()]
FROM Employers
FOR XML PATH ('')),1,1,'')

answered Apr 08 '13 at 13:55

TEEKAY

1,156
1
10
25

How to get the result of a query which has several lines as a result in one single line separated by one separator?

2 Answers2