SQL Server 2008 merge all rows into one

Question

I have one column in my query and multiple rows (100+). I need to have the rows combined with only a space between so that I end up with one row.

Example:

1
2
3
4
5

Would actually be

1 2 3 4 5

Answer 1

You can try to use STUFF function.

CREATE TABLE T (Id int)

INSERT INTO t VALUES (1);
INSERT INTO t VALUES (2);
INSERT INTO t VALUES (3);
INSERT INTO t VALUES (4);
INSERT INTO t VALUES (5);

Query

SELECT 
  STUFF((
    SELECT ' ' + CAST(Id AS VARCHAR(MAX))  
    FROM T
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,0,'') 

sqlfiddle