Friday, February 3, 2017

Simple example of Pivot table in SQL Server

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

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

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

Simple example of Pivot table in SQL Server

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

Simple example of Pivot table in SQL Server

No comments:

Post a Comment