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:-
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:-
Avinash Dubey 30 20 35
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, , [II], [III], [IV] , [V]
from
( Select grade, studentname, marks from tbl_student) as sourcetable
Pivot ( avg(marks) for grade in (,[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, , [II], [III], [IV] , [V] from tbl_student
Pivot ( avg(marks) for grade in (,[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)
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
