Merge data many rows of one column into a single row in sql server

Question

I have a table like

create table temp_table (col1 int)

I have some data in this table like

insert into temp_table 
values(1), (2), (3), (4)

Now I want the data to be output as follows:

1,2,3,4

I have used the query:

select cast(col1 as nvarchar)+',' 
from temp_table 
for xml path('')

The problem is that the output comes in XML format and i need it in simple text/string format. I tried searching for transpose but FOR XML() was mentioned everywhere and it didn't helped me.

Any suggestions?

take a look here: http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — rvandoni, Jul 17 '14 at 10:04
You need STUFF and XML_PATH, like there : http://stackoverflow.com/a/17591536/961526 — Raphaël Althaus, Jul 17 '14 at 10:05

Ashok Damani · Accepted Answer · 2014-07-17T10:12:50.747

3

declare @temp nvarchar(max)
select @temp = COALESCE(@temp + ', ', '') + CAST(col1 as nvarchar) from temp_table
select @temp

edited Jul 17 '14 at 10:12

answered Jul 17 '14 at 10:07

Ashok Damani

3,852
4
27
48

@Raphaël Althaus yups !!, see updated... – Ashok Damani Jul 17 '14 at 10:13

score 3 · Answer 2 · answered Jul 17 '14 at 10:07

3

And if you really don't get it with the given link :

select STUFF((
          SELECT ',' + cast(col1 as nvarchar)
          FROM temp_table
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

answered Jul 17 '14 at 10:07

Raphaël Althaus

58,557
6
89
116

score 1 · Answer 3 · answered Jul 17 '14 at 10:07

1

Try this Query

Select STUFF((SELECT ',' + Cast(col1 As nvarchar) FROM temp_table 
          FOR XML PATH('')), 1, 1, '') As MyColumn

answered Jul 17 '14 at 10:07

Shell

6,680
10
36
70

Merge data many rows of one column into a single row in sql server

3 Answers3