How to Use Pivot Table in sql server.

In this article I will be explaining you about Pivot table. Pivot Table can make a dynamic Query using that you can do calculation. Pivot table feature in SQL is very important while making SQL server report.

Below is simple example to know how to use Pivot Table in SQL.

create table Salary(ID int identity,EmpID int,SYear Int,SMonth int,EmpSalary decimal(24,2))

Insert into Salary values(101,2012,1,12500),(101,2012,2,12000),(101,2012,3,150000)
Insert into Salary values(102,2012,1,22500),(102,2012,2,25000),(102,2012,3,25000)
Insert into Salary values(101,2011,1,12500),(101,2011,2,12000),(101,2011,3,150000)
Insert into Salary values(102,2011,1,22500),(102,2011,2,25000),(102,2011,3,25000)

Create Proc MakeDynamicPivotTable
select Distinct SYear into #Temp from Salary
declare @AllYearFetch varchar(500)
select @AllYearFetch=coalesce(@AllYearFetch+',['+CAST(SYear as varchar)+']','['+CAST(SYear as varchar)+']') from #Temp
print @AllYearFetch
declare @pivot nvarchar(max)
set @pivot='select * from
select EmpID,SYear,EmpSalary from Salary
) p
(sum(EmpSalary) for SYear in('+@AllYearFetch+')) as PivotTable'
print @pivot

Above query gives you the output like This,

EmpID 2011 2012
101 174500.00 174500.00
102 72500.00 72500.00


No responses found. Be the first to comment...

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: