Sql Multiple Rows Into One Row

| Posted on by



  1. There are three rows Id marks Row1: 1 15 Row2: 1 16 Row3: 1 17 and I want to merge all the three rows into a single row, can anybody help me please!!!! Output should be like below Id marks1 marks2 marks3 Row1: 1 15 16 17 please help me.
  2. Multiple rows to single row in sql server 2008 using STUFF function How can I return multiple rows into single rows as per date and departmentid? Split a single row into multiple row based on column value.
  1. Sql Two Rows Into One
  2. How To Sum Multiple Rows
  3. Sql Concat Multiple Rows Into One Row
  4. T Sql Multiple Rows Into One Row
MultipleImagine you have a column like this:

Re: Multiple SQL rows merge into single row if the id is same?? Jul 19, 2010 02:33 PM rajsedhain LINK I have another problem now, if the unit name is more than a single word then it adds comma after each word.

Sql Two Rows Into One

Numbers
---------

How To Sum Multiple Rows


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:Rows
-- 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

Sql Concat Multiple Rows Into One Row


DECLARE @str VARCHAR(100)
SELECT @str = COALESCE(@str + ', ') + [Numbers]
FROM #Temp
Print @str
You can also achieve the same result using STUFF

T Sql Multiple Rows Into One Row

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

I was preparing a statistical report and was stuck in one place where I needed to convert certain rows to comma separated values and put into a single row.

Lets say I have multiple currencies in a table and I would like to display in a single comma separated rows.

I found few options to resolve this.

  1. USING CURSOR
  2. USING COALESCE
  3. USING STUFF

Let me create a simple example to explain these methods in detail.

Let me explain these methods in detail :

    • USING CURSOR:

This is a simple way for development but performance wise it will costa lot for your application.

Given below is a CURSOR example :

    • USING COALESCE:

We can also achieve the same using COALESCE function. Given below is an example.

    • USING STUFF:

This is the recommended / best way to do this because you can achieve the same result without any variable and less lines of code. Given below is an example.

RESULT :
The result of all of the above methods is the same. Given below is the result.

  • REAL WORLD EXAMPLE :

As mentioned above, I was working on a report where multiple Bank A/C # should be displayed next to the customer name like a comma separated field.

Let me create an example to explain this :

Lets combine the account number (comma separated values) with respect to the customer.