pivoting in SQL

Table Structure:
emp_id,emp_name, year,month,basic,da,hra,pf,pt

[Emp Salary = basic + da + hra – ( pf + pt )]

Create a sp to find the following output.

Last Year This Year Last Year This Year
Emp1 Salary
Emp2 Salary
... … … … …
Emp n Salary
Total ∑ ∑ ∑ ∑

Solution:

create table EMP(
[emp_id] int,
[emp_name] [varchar](30),
[year] int,
[month] smallint,
[basic] int,
[da] int,
[hra] int,
[pf] int, [pt] int)

insert into EMP values(1,'John Carter',2011,1,23000,8000,8000,750,450)
insert into EMP values(1,'John Carter',2011,2,23000,8000,8000,750,450)
insert into EMP values(1,'John Carter',2011,3,23000,8000,8000,750,450)

insert into EMP values(1,'John Carter',2011,7,23000,8000,8000,750,450)
insert into EMP values(1,'John Carter',2011,8,23000,8000,8000,750,450)
insert into EMP values(1,'John Carter',2011,9,23000,8000,8000,750,450)

insert into EMP values(1,'John Carter',2012,1,28000,9500,8500,950,500)
insert into EMP values(1,'John Carter',2012,2,28000,9500,8500,950,500)
insert into EMP values(1,'John Carter',2012,3,28000,9500,8500,950,500)

insert into EMP values(1,'John Carter',2012,7,28000,9500,8500,950,500)
insert into EMP values(1,'John Carter',2012,8,28000,9500,8500,950,500)
insert into EMP values(1,'John Carter',2012,9,28000,9500,8500,950,500)


insert into EMP values(2,'Marvin King',2011,1,28000,9500,8500,950,500)
insert into EMP values(2,'Marvin King',2011,2,28000,9500,8500,950,500)
insert into EMP values(2,'Marvin King',2011,3,28000,9500,8500,950,500)

insert into EMP values(2,'Marvin King',2011,7,28000,9500,8500,950,500)
insert into EMP values(2,'Marvin King',2011,8,28000,9500,8500,950,500)
insert into EMP values(2,'Marvin King',2011,9,28000,9500,8500,950,500)

insert into EMP values(2,'Marvin King',2012,1,32000,10500,9500,1100,800)
insert into EMP values(2,'Marvin King',2012,2,32000,10500,9500,1100,800)
insert into EMP values(2,'Marvin King',2012,3,32000,10500,9500,1100,800)

insert into EMP values(2,'Marvin King',2012,7,32000,10500,9500,1100,800)
insert into EMP values(2,'Marvin King',2012,8,32000,10500,9500,1100,800)
insert into EMP values(2,'Marvin King',2012,9,32000,10500,9500,1100,800)

select emp_name,Q12011,Q12012,Q22011,Q22012,Q32011,Q32012,Q42011,Q42012 into #pivotTemp from(
select
emp_name,

case when month>=1 and month<=3 then 'Q1'
when month>=4 and month<=6 then 'Q2'
when month>=7 and month<=9 then 'Q3'
when month>=10 and month<=12 then 'Q4'
end+ convert(varchar,year) AS quarterYear,

SUM (isnull(basic + da + hra - ( pf + pt ),0)) As Sal

from EMP
group by emp_name, year,case when month>=1 and month<=3 then 'Q1'
when month>=4 and month<=6 then 'Q2'
when month>=7 and month<=9 then 'Q3'
when month>=10 and month<=12 then 'Q4'
end+ convert(varchar,year)
--order by emp_name,quarterYear
) ali

pivot
( sum(Sal) FOR quarterYear in (Q12011,Q12012,Q22011,Q22012,Q32011,Q32012,Q42011,Q42012) ) as pvt1


select isnull(EMP_name,'GRAND TOTAL'),sum(isnull(Q12011,0)) as Q12011,sum(isnull(Q12012,0)) as Q12012,sum(isnull(Q22011,0)) as Q22011,
sum(isnull(Q22012,0)) as Q22012,sum(isnull(Q32011,0)) as Q32011,sum(isnull(Q32012,0)) as Q32012,sum(isnull(Q42011,0)) as Q42011,sum(isnull(Q42012,0)) as Q42012 from #pivotTemp
group by EMP_name
with rollup

drop table #pivotTemp


To make it more generic we need to build the above query dynamically an pass it to exec function. for ex: exec ('select * from EMP')