PIVOT TABLE Syntax

[KirbyWallace]

Hi All!

I need to transform this:
in this example, the type is 1=vac, 2=sick, 3=paid leave

Date              Hours         Type
8/21/2009         3.5           2
8/21/2009         4.5           3
11/16/2009        8             1
11/17/2009        8             1
11/18/2009        8             1

Into this:

Date              Vac    Sick    PaidLeave
8/21/2009         0      3.5     4.5
11/16/2009        8      0       0
11/17/2009        8      0       0
11/18/2009        8      0       0

I understand this is a pivot table operation, but I've never done one before. Any ideas?

I'm looking for TSQL code - a SELECT statement approach.


Thanks for any help you can offer.

 

[RiverGuy]

SELECT [Date],
SUM(Vac) AS Vac,
SUM(Sick) AS Sick,
SUM(PaidLeave) AS PaidLeave
FROM
		(SELECT [Date],
		CASE
		  WHEN [Type] = 1 THEN [Hours]
		  ELSE 0
		END AS Vac,
		CASE
		  WHEN [Type] = 2 THEN [Hours]
		  ELSE 0
		END AS Sick,
		CASE
		  WHEN [Type] = 3 THEN [Hours]
		  ELSE 0
		END AS PaidLeave
		FROM SomeTable) x
GROUP BY [Date]
ORDER BY [Date]

 

[KirbyWallace]

I should mention that I've already gotten as far as this:

SELECT date_Time, [1] as VAC,[2] as SICK,[3] as Paid,[4] as Unpaid
FROM tbl_Employee_Absences
PIVOT (
Sum(Absence_Hours) FOR Absence_Type IN ([1],[2],[3],[4])
) AS TableName

Which works except for combining the rows for the one date into one row.

 

[KirbyWallace]

Thanks RiverGuy...

Here's what I have that does work. Got it figured out after posting my reply. I was missing the SUMS and GROUP BY.

This is what I've got now. Do you think this is doing what I think it is? Or is it gonna do something I'm not thinking its gonna do at some point down the road? (Bring forth the Holy Hand Grenade! ;-)

SELECT date_Time, sum([1]) as VAC,sum([2]) as SICK,sum([3]) as Paid,sum([4]) as Unpaid
FROM tbl_Employee_Absences
PIVOT (
Sum(Absence_Hours) FOR Absence_Type IN ([1],[2],[3],[4])
) AS TableName
GROUP BY date_time

 

[RiverGuy]

Yeah that should work fine.