Combine multiple rows into one row using SQL Server

Imagine you have a column like this:

Numbers
---------
One
Two
Three
Four
Five

The output you desire is to combine all the rows and put it as one row similar to the following:

One|Two|Three|Four|Five


Let us see how to do it:

-- Sample Script to create the table and insert rows
CREATE TABLE #Temp
(
[Numbers] varchar(40)
)
INSERT INTO #Temp VALUES('One');
INSERT INTO #Temp VALUES('Two');
INSERT INTO #Temp VALUES('Three');
INSERT INTO #Temp VALUES('Four');
INSERT INTO #Temp VALUES('Five');


-- Query to combine multiple rows into one
DECLARE @str VARCHAR(100)
SELECT @str = COALESCE(@str + '|', '') + [Numbers]
FROM #Temp
Print @str

You can also achieve the same result using STUFF

SELECT DISTINCT STUFF( (SELECT '*' + Numbers from #Temp FOR XML PATH('')),1,1,'') as Numbers FROM #Temp