Create table #UserLeave (UserID int, LeaveDate datetime, PortionOfDay decimal(8,2), ReasonID int)  
Create table #UserLeaveReasons (ReasonID int, [ReasonText] varchar(100), [Group] varchar(100))  

Insert Into #UserLeaveReasons values (1, 'Vacation', 'Vacation')
Insert Into #UserLeaveReasons values (2, 'Sick', 'Sick')
Insert Into #UserLeaveReasons values (3, 'Maternity', 'Other')
Insert Into #UserLeaveReasons values (4, 'Jury Duty', 'Other')

Insert Into #UserLeave values (123, '06/06/2007', 0.5, 2)
Insert Into #UserLeave values (123, '06/08/2007', 1.0, 1)
Insert Into #UserLeave values (123, '06/13/2007', 0.5, 1)
Insert Into #UserLeave values (123, '06/14/2007', 1.0, 2)
Insert Into #UserLeave values (123, '06/15/2007', 1.0, 3)
Insert Into #UserLeave values (123, '06/26/2007', 1.0, 4)

Insert Into #UserLeave values (123, '06/06/2006', 1.0, 3)
Insert Into #UserLeave values (123, '06/08/2006', 1.0, 1)
Insert Into #UserLeave values (123, '06/13/2006', 1.0, 1)
Insert Into #UserLeave values (123, '06/14/2006', 0.5, 3)
Insert Into #UserLeave values (123, '06/15/2006', 0.5, 3)
Insert Into #UserLeave values (123, '06/26/2006', 0.5, 4)

Insert Into #UserLeave values (222, '06/06/2007', 0.5, 2)
Insert Into #UserLeave values (222, '06/08/2007', 1.0, 1)
Insert Into #UserLeave values (222, '06/13/2007', 0.5, 1)
Insert Into #UserLeave values (222, '06/14/2007', 1.0, 2)
Insert Into #UserLeave values (222, '06/15/2007', 1.0, 3)
Insert Into #UserLeave values (222, '06/26/2007', 1.0, 4)

Insert Into #UserLeave values (222, '06/06/2006', 1.0, 2)
Insert Into #UserLeave values (222, '06/08/2006', 1.0, 1)
Insert Into #UserLeave values (222, '06/13/2006', 1.0, 1)
Insert Into #UserLeave values (222, '06/14/2006', 0.5, 2)
Insert Into #UserLeave values (222, '06/15/2006', 0.5, 3)
Insert Into #UserLeave values (222, '06/26/2006', 0.5, 4)

-- I want to query by UserID
-- Considering all groups of #UserLeaveReasons, I need to display the total # of leave for each year in a seperate column.
-- Assume only the year 2006 & 2007 data is present.
-- I need the results set when queried as follows (queried for the userid = 123)
-- Also I need it in the same order as given below ( I can put a Column to do "Order By " in #UserLeaveReasons

Leave Type		2006	       2007
---------		----	       ----
Vacation		2.0		1.5
Sick			0.0		1.5
Other			2.5		2.0

Select	[Group],  
		Sum(Case When LeaveDate between '2006-01-01' and '2006-12-31' then PortionOfDay else 0 end ) as  [2006], 
		Sum(Case When LeaveDate between '2007-01-01' and '2007-12-31' then PortionOfDay else 0 end ) as  [2007], 
		Sum(Case When LeaveDate between '2008-01-01' and '2008-12-31' then PortionOfDay else 0 end ) as  [2008] 
from #UserLeave ul 
left Join #UserLeaveReasons ulr On ulr.[ReasonID] = ul.[ReasonID] 
where UserID = 123
Group By [Group]

Select	ulr.[Group],  
		Sum(Case When ul.LeaveDate between '2006-01-01' and '2006-12-31' then ul.PortionOfDay else 0 end ) as  [2006], 
		Sum(Case When ul.LeaveDate between '2007-01-01' and '2007-12-31' then ul.PortionOfDay else 0 end ) as  [2007], 
		Sum(Case When ul.LeaveDate between '2008-01-01' and '2008-12-31' then ul.PortionOfDay else 0 end ) as  [2008] 
from #UserLeaveReasons ulr  
left Join #UserLeave ul On ulr.[ReasonID] = ul.[ReasonID] and ul.UserID = 123
Group By ulr.[Group]