Saturday, April 14, 2012

Pivot and Unpivot table in SQL SERVER


Pivot Table:- Pivot tables are used to summarize and display the data, specially in case of report data by means of aggregating the values. Pivot table can be used if we want to display the unique values of the column of a table as the columns of another table. It turns the unique values of a specified column into another table columns.


The syntax for the Pivot is given below:-



SELECT non-pivoted column,
    firstpivotedcolumn AS column name,
    secondpivotedcolumn AS column name,
    lastpivotedcolumn AS column name
FROM
    (SELECT query that produces the data>)
    AS aliasforsourcequery
PIVOT
(
    aggregation function(column being aggregated)
FOR
column that contains the values that will become column headers
    IN ( firstpivotedcolumn,secondpivotedcolumn,
    last pivoted column)
) AS aliasforthepivottable (optional ORDER BY clause) 



For example, suppose we have a table called tbl_student which contains the columns studentname, grade and marks. The query for creating this table and inserting data is given below:-


Syntax for creating the database:-


Create database DB_Pivot


Query for creating table:-

Create table tbl_student (studentname nvarchar(200), grade nvarchar(10), marks int)


Query for inserting the data into the table:-

Insert into tbl_student (studentname,grade,marks)
Select 'Vivek Johari','I',30
Union All
Select 'Vivek Johari','II',20
Union All
Select 'Vivek Johari','III',35
Union All
Select 'Vivek Johari','IV',40
Union All
Select 'Vivek Johari','V',45
Union All
Select 'Avinash Dubey','I',30
Union All
Select 'Avinash Dubey', 'II', 2
Union All
Select 'Avinash Dubey', 'III' ,35
Union All
Select 'Chandra Singh', 'I', 30
Union All
Select 'Chandra Singh', 'II', 20
Union All
Select 'Chandra Singh', 'III', 35
Union All
Select  ' Pankaj Kumar', 'I', 33
Union All
Select ' Pankaj Kumar', 'II', 29


Now if we want to see the data in the table tbl_student, it will looks like shown below:-


Select  *  from tbl_student




Suppose we want to display the data as shown below:- 

Studentname       I            II               III             IV                  V
Vivek Johari          30        20           35          40              45
Chandra Singh      30        20           35
Avinash Dubey      30        20           35                       
Pankaj Kumar       33         29 


Then we can either use the Select......... Case statement or the Pivot command.
In this article I am going to show the use of the Pivot operator to display data as shown above:-


Select studentname, [I], [II], [III], [IV] , [V]
 from
( Select grade, studentname, marks from tbl_student) as sourcetable
Pivot (  avg(marks) for grade in ([I],[II],[III],[IV],[V])) as pivotable order by V desc,IV desc,III desc,II desc,I desc





Or we can use the given below query also:-



Select studentname, [I], [II], [III], [IV] , [V] from tbl_student
Pivot  (  avg(marks) for grade in ([I],[II],[III],[IV],[V])) as pivotable order by V desc,IV desc,III desc,II desc,I desc








Both the query will gives the same result. In the first query we use the Derived table as the Source table and in the 2nd query we use the table tbl_student as the source table.


Unpivot table:- Unpivot table is reverse of Pivot table as it rotate the columns of a table into the value of a column. For example, suppose we have a table say tbl_stdmarksdata whose structure us given below:-



Create table tbl_stdmarksdata  (studentname nvarchar(100), I int, II int, III int, IV int, V int)

Query for inserting data in this table is given below:-

Insert into tbl_stdmarksdata (studentname,I,II,III,IV,V)
Select 'Vivek Johari',30,20,35, 40, 45
Union All
Select 'Chandra Singh',30,20,35,44, 80
Union All
Select 'Avinash Dubey',30,25,35,20, 39
Union All
Select 'Pankaj Kumar',33,29,30, 60, 50

After insert,  the data in the table :-

select * from tbl_stdmarksdata 



The Query for the Unpivot table will be as follow:-

select studentname,Marks,Grade
from tbl_stdmarksdata
unpivot
(Marks for Grade in (I,II,III,IV,V) ) as tblunpvt







Please  note:- Also as per the MSDN,
When PIVOT and UNPIVOT are used against databases that are upgraded to SQL Server 2005 or later, the compatibility level of the database must be set to 90 or higher.


Futher reading about the Pivot operator can be done at the following Pivot Operator

DMCA.com

11 comments:

  1. Finding hard to learn anything in database……..Just come to vivek’s blog and its guaranteed that will get the solution in simplest way . Again very nice article :)

    ReplyDelete
  2. main difference pivot and unpivot purpose....

    ReplyDelete
  3. HI Friends,

    I need one small help,

    Month Babu Kumar revenue
    1 10 125 E14+E15
    2 21 54 E15+E16
    3 5 5 E16+E17
    4 2 21 E17+E18
    5 12 56 E18+E19

    How to use pivot and unpivot

    ReplyDelete
  4. Explained with real time example,
    http://www.negablog.com/2013/01/unpivot-table-in-sql-server.html
    http://www.negablog.com/2013/01/pivot-table-using-sql-server.html

    ReplyDelete
  5. Hi Vivek, Very nice article
    ~Mayur

    ReplyDelete
  6. Thanks Vivek, It is nice and simple article so understanding is better

    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete
  8. Is it possible to update an unnormalized table from normalized table using a SQL UPDATE statement and Unpivot function?

    I have two tables T1 and T2.

    T1 has data row headings segmentID, Segment, [201401],][201402]...
    T2 has columns YearMo, rate ...

    I want to be able to unpivot T1 and update the [201401]...columns with data from T2.

    Thank you for any help!!
    Christina

    ReplyDelete
    Replies
    1. Hi Crissy, Yes it is very much possible to unpivot the table T1. can you give more details about the update you want from table T2

      Regards
      Vivek

      Delete