Simple example of Pivot table in SQL Server

In this article I am going to explain how to create Pivot table in SQL Server.

In previous article I have explained how to draw Pie chart using Google chart inMVC application, how to Check Case-Sensitivity in SQL Server, how to create commaseparated list in Sql server, how to encrypt store procedure, view and functionin sql server and how to take database Backup and restore using command T-Sql.

Description:

Pivot query used to show data in tubular format. Means it will convert the multiple rows into multiple columns using aggregate function.

Example:

I have created a temporary table and insert some dummy data into it.

create table #empsalary

(

id int,name varchar(50),salary int, [monthname] varchar(50),[year] varchar(50)

)

insert into #salary values(1,'john',10000,'January','2016')

insert into #salary values(2,'john',11000,'February','2016')

insert into #salary values(3,'john',12000,'March','2016')

insert into #salary values(4,'john',10000,'April','2016')

insert into #salary values(5,'john',13000,'May','2016')

insert into #salary values(6,'john',11000,'June','2016')

insert into #salary values(7,'john',10000,'July','2016')

insert into #salary values(9,'john',10000,'August','2016')

insert into #salary values(10,'john',15000,'September','2016')

insert into #salary values(11,'john',15000,'November','2016')

insert into #salary values(12,'john',20000,'December','2016')

insert into #salary values(13,'john',20000,'January','2017')

insert into #salary values(14,'john',20000,'February','2017')

insert into #salary values(15,'john',20000,'March','2017')

Select the record

Select * from #empsalary

Create Pivot query

Select * from(Select [year],salary,[monthname] from #empsalary)pv pivot(sum(salary) FOR [monthname] IN

(January,February,March,April,May,June,July,August,September,November,December)) as pvt

Output: