Pivot and Unpivot table in SQL SERVER

Most Imp SQL Concepts SQL Pivot and Unpivot table 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’, 29Now 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.

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

Also if you want to read about SQL Pivot and Unpivot in Oracle, Ben Brumm has written a very good article Oracle SQL PIVOT and UNPIVOT: The Complete Guide”

DMCA.com

12 thoughts on “Pivot and Unpivot table in SQL SERVER

  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 🙂

  2. 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

  3. 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

  4. Vivek, thank you for the clearest, most easy to follow explanation I have found for setting up a pivot query.

Leave a Reply