Simple example of Pivot table in SQL Server - Free Asp.Net,MVC,AngularJs,Jquery,Javascript,Sql Server,WCF,Entity framework snippets and tutorial


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


What do you think about this article?

If you found this article useful, please share and follow on Facebook, Twitter, Google Plus and other social media websites. To get free updates subscribe to newsletter. Please put your thoughts and feedback in comments section.

Share this

Share on FacebookTweet on TwitterPlus on Google+
loading...


EmoticonEmoticon