SQL: How to get a column's values of all rows as a single value?

Question

I am working with SQL Server 2008 R2.

This is my table:

CREATE TABLE step
(
   id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
   step_value nvarchar(max) NOT NULL
)

Currently there are 197 rows in step table. I want to get all values of step_value column as a single value with a semi-colon delimiter. How can I do that?

Some sample data:

Insert Into step(step_value)
Values ('a'),('b'),('c'),('d'),('<workflow name="DISCIPLINE_LETTER">'), ('   <target>MEETING_INITIAL_MEETING_LETTER</target> ')

Thanks

Mihai · Accepted Answer · 2016-04-14T17:15:00.997

0

Use STUFF function

select stuff(
 (select '; ' + step_value 
 from step
 for xml path(''), root('MyString'), type
 ).value('/MyString[1]','nvarchar(max)')
, 1, 2, '') as step_values ;

FIDDLE

edited Apr 14 '16 at 17:15

answered Apr 14 '16 at 16:46

Mihai

26,325
7
66
81

this did not work. – srh Apr 14 '16 at 16:51
thanks. but i have characters like < and > in the column and it does not work as it is translating them to < and >. I need the exact values – srh Apr 14 '16 at 16:57
@srh Add some sample data in my fiddle – Mihai Apr 14 '16 at 16:57
I have added a sample data to your fiddle – srh Apr 14 '16 at 17:01
My sample data cannot be saved in your fiddle. So I have put the sample data in the question above. – srh Apr 14 '16 at 17:06
wow this is great. i want to learn this xml path root stuff. can you point me to a great tutorial about this stuff? – srh Apr 23 '16 at 19:37

score 0 · Answer 2 · answered Apr 14 '16 at 16:53

0

SELECT TOP 1 (STUFF((SELECT ', ' + CAST(id AS VARCHAR(MAX)) 
         FROM step 
         FOR XML PATH ('')), 1, 2, '')) AS a
FROM step

answered Apr 14 '16 at 16:53

PollusB

1,726
2
22
31

SQL: How to get a column's values of all rows as a single value?

2 Answers2