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
as
Begin
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
pivot
(sum(EmpSalary) for SYear in('+@AllYearFetch+')) as PivotTable'
print @pivot
exec(@pivot)
End
Above query gives you the output like This,
EmpID 2011 2012
101 174500.00 174500.00
102 72500.00 72500.00