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.

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

Query for creating table:-

Create table tbl_employee (name nvarchar(200), grade nvarchar(10), sal numeric(18,2)) 
 Query for inserting data in table:-

insert into tbl_employee
select 'sandeep','A','5000'
union all
select 'sandeep','B','4000'
union all
select 'sandeep','C','3000'
union all
select 'Kumar','B','2000'
union all
select 'Yadav','A','5000'

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

Select  *  from tbl_employee
 




select name,A,B,C  from tbl_employee
pivot
(
 avg(sal)for grade in([A],[B],[C])
)as pvt
order by A,B,C DESC

Result will display as:

 

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_SalData whose structure us given below:- 

Create table tbl_SalData  (name nvarchar(100), A Numeric(18,2), B Numeric(18,2), C Numeric(18,2)) 

Query for inserting data in table:-

insert into tbl_SalData
select 'sandeep',5000,4000,3000
UNION ALL
select 'Kumar',5000,4000,3000
UNION ALL
select 'Yadav',5000,4000,3000

After insert,  the data in the table :-

select * from tbl_SalData

Query for UNPIOT

select name,sal,Grade
from tbl_SalData
unpivot
(sal for Grade in (A,B,C) ) as tblunpvt

Result will display as: