How to convert columns into rows

Question

0.00/5 (No votes)

See more:

Hello friends i found that this code project sites provides better solutions and help that makes development more easy and this site helps to comunicate with other professional developers so they can share their important knowledge who requires it I want to say thanks to the developers and code project for their great work.
I'm having a table in that I want to convert columns into rows

+--------------+--------------+--------------+--------------+
|(Column Name1)|(Column Name2)|(Column Name3)|(Column Name4)|
+--------------+--------------+-------------+---------------+
|Data11        |Data21        |Data31       |Data41         |
+--------------+--------------+-------------+---------------+
|Data12        |Data22        |Data32       |Data42         |
+--------------+--------------+-------------+---------------+
|Data13        |Data23        |Data33       |Data43         |
+--------------+--------------+-------------+---------------+
|Data14        |Data24        |Data34       |Data44         |
+--------------+--------------+-------------+---------------+
|Data15        |Data25        |Data35       |Data45         |
+--------------+--------------+-------------+---------------+

The above table column should be look like below table:

+--------+--------+--------+--------+--------+
| (Col1) | (Col2) | (Col3) | (Col4) | (Col5) |
+--------+--------+--------+--------+--------+
| Data11 | Data12 | Data13 | Data14 | Data15 |
+--------+--------+--------+--------+--------+
| Data21 | Data22 | Data23 | Data24 | Data25 |
+--------+--------+--------+--------+--------+
| Data31 | Data32 | Data33 | Data34 | Data35 |
+--------+--------+--------+--------+--------+
| Data41 | Data42 | Data43 | Data44 | Data45 |
+--------+--------+--------+--------+--------+

I tried below query but it works only for single column i want multiple columns to be converted in rows.

What I have tried:

Declare @cols nvarchar(max)
Declare @query nvarchar(max)

Select @cols = stuff((select ','+QuoteName(Row_Number() over (Order by (Select NULL))) from #cols for xml path('')),1,1,'')
Select @query = ' Select * from (
    Select colname, RowN = Row_Number() over (order by colname) from #cols
    ) a
    pivot (max(colname) for RowN in (' + @cols + ')) p '

Exec sp_executesql @query

Please help me for the solution.

Posted 18-Dec-18 19:30pm

Member 12340963

Updated 19-Dec-18 5:14am

RickZeeland

v2

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dirk Bahle · Answer 1 · 2018-12-19T05:15:00

I think what you want is an unpivot and not a pivot, what about the following code:

SQL

-- Create the table and insert values as portrayed in the previous example.  
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,  
    Emp3 int, Emp4 int, Emp5 int);  
GO  
INSERT INTO pvt VALUES (1,4,3,5,4,4);  
INSERT INTO pvt VALUES (2,4,1,5,5,5);  
INSERT INTO pvt VALUES (3,4,3,5,4,4);  
INSERT INTO pvt VALUES (4,4,2,5,5,4);  
INSERT INTO pvt VALUES (5,5,1,5,5,5);  
GO  
-- Unpivot the table.  
SELECT VendorID, Employee, Orders  
FROM   
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5  
   FROM pvt) p  
UNPIVOT  
   (Orders FOR Employee IN   
      (Emp1, Emp2, Emp3, Emp4, Emp5)  
)AS unpvt;  
GO

taken from here: FROM - Using PIVOT and UNPIVOT | Microsoft Docs[^]

Does this work for you and if not what is the table definition, expected result, and query you are using - please try to do something like in the above code snippet because its realy difficult to impossible to help if you cannot give the relevant details...

How to convert columns into rows

1 solution

Solution 1

Add your solution here

Preview 0

How to convert columns into rows

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us