Sqlserver notes : Pivot and Unpivot (converting horizontal rows to vertical columns)

Source :

 

http://www.sqlservercentral.com/blogs/vivekssqlnotes/2012/04/14/pivot-and-unpivot-table-in-sql-server/

 

http://shivasoft.in/blog/sql/sqlserver/sql-server-pivot-table-example/

 

http://www.c-sharpcorner.com/Blogs/9783/how-to-use-pivot-table-in-sql-server.aspx

 

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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: