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