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:

OneTwoThreeFourFive


Let us see how to do it:

-- Sample Script to create the table and insert rows
-- By SQLServerCurry.com

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

Update: Here's another solution if you want to combine and output multiple rows as CSV - SQL Server: Combine Multiple Rows Into One Column with CSV output

Did you like this post?

	subscribe via rss		subscribe via e-mail
	print this post		follow me on twitter

Feedback: