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.


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


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


Output:
Simple example of Pivot table in SQL Server


No comments:

Post a Comment