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

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?

Answer 1

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

Answer 2

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, '')

Answer 3

Try this Query

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