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

No Comments Yet.

Leave a reply

You must be logged in to post a comment.