Unpivoting Data in Sql

Unpivoting Data in Sql

My Previous Blog was  on Pivoting Data in SQL Today I will explain you what is Unpivoting of Data

Unpivoting of Data Mean changing State of Columns to State of Row which means displaying Column Names as Row Data.In order to know what's Pivoting of Data Read my Previous blog

http://sqlandssrssolutions.blogspot.in/#!/2013/08/pivoting-data-in-sql.html

So Let me Explain through Example what does Unpivoting of Data Mean

We will Use the Output of Pivoting Data into a table
Create table Emp

(

empid int ,

custid nvarchar(30),

qty int

 )

Insert into Emp values (1,'A',10) ,

(2,'b',5),

(3,'c',2),

(4,'A',6),

(5,'b',1),

(6,'c',6),

(7,'d',6), 

(1,'A',12) ,

(2,'b',15)

Select * into Emp1 from

 (

select empid,A,B,C,D from

   (

        select empid,custid,qty from emp

    ) As D

pivot(sum(qty) for custid in (A,B,C,D)) as P

   )D

This Script will create a new table name Emp1
Select * from Emp1
Output

empid A B     C            D

1 22 NULL NULL NULL

2 NULL 20 NULL NULL

3 NULL NULL 2 NULL

4 6 NULL NULL NULL

5 NULL 1 NULL NULL

6 NULL NULL 6 NULL

7 NULL NULL NULL 6

Note: This is the same output which we get after pivoting of Data in our previous blog.In order to get desired Output we will use Unpivot Operator as below

Select empid,custid,Qty from Emp1 unpivot(qty for Custid in (A,B,C,D)) as UNP;

Desired Output (i.e. Unpivoting of Data)

empid custid Totalqty

1           A                22

4           A                6

2           b                20

5           b                1

3          c                  2

6          c                  6

7          d                  6

Explanation: The Unpivot Query in Select clause write the name which you want to display. Un pivot will Sum the qty in QTY columns based on Cust Id and the Cust Id will hold Column Names which are inside the In clause.