Click here to Skip to main content
14,301,895 members
Rate this:
Please Sign up or sign in to vote.
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
Updated 19-Dec-18 5:14am
v2

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

I think what you want is an unpivot and not a pivot, what about the following code:
-- 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...
   
v3

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100