SQL Complex Pivot
This is a sample for a complex pivot
Declare @Employees table(id int,name varchar(40))
Insert Into @Employees
Values (10,'John')
Insert Into @Employees
Values (20,'Luis')
Insert Into @Employees
Values (30,'Sebastian')
Insert Into @Employees
Values (40,'Richard')
Insert Into @Employees
Values (50,'Gonzalo')
Insert Into @Employees
Values (60,'Leo')
Declare @MonthlySales Table(id int identity (1,1),empid int,totalsales int,salesdate datetime)
Insert into @MonthlySales
Values(10,15,'01/31/2012')
Insert into @MonthlySales
Values(10,2,'06/30/2011')
Insert into @MonthlySales
Values(10,4,'03/31/2010')
Insert into @MonthlySales
Values(20,5,'01/31/2012')
Insert into @MonthlySales
Values(20,3,'02/10/2011')
Insert into @MonthlySales
Values(20,20,'03/15/2010')
Insert into @MonthlySales
Values(30,30,'04/20/2012')
Insert into @MonthlySales
Values(30,3,'05/15/2011')
Insert into @MonthlySales
Values(40,22,'06/9/2012')
Insert into @MonthlySales
Values(40,28,'07/1/2011')
Insert into @MonthlySales
Values(40,24,'05/5/2010')
Insert into @MonthlySales
Values(50,22,'11/9/2012')
Insert into @MonthlySales
Values(50,10,'12/22/2012')
Insert into @MonthlySales
Values(60,11,'07/16/2012')
Insert into @MonthlySales
Values(60,14,'10/2/2011')
Insert into @MonthlySales
Values(60,9,'04/3/2010')
Insert into @MonthlySales
Values(60,15,'09/4/2011')
--Select * from @Employees
--select * from @MonthlySales
Select name as YearlySales,[2010],[2011],[2012]
FROM (
Select name, totalsales,year(salesdate) as yearlysales
from @Employees e join @MonthlySales m on e.id=m.empid
) p
PIVOT
(
sum(totalsales)
for yearlysales IN ([2010],[2011],[2012])
) as pvt
order by pvt.name
The result is:
YearlySales | 2010 | 2011 | 2012 |
Gonzalo | NULL | NULL | 32 |
John | 4 | 2 | 15 |
Leo | 9 | 29 | 11 |
Luis | 20 | 3 | 5 |
Richard | 24 | 28 | 22 |
Sebastian | NULL | 3 | 30 |