Pivot Table in Sql Server 2005

New but not new since sql server 2008 have been launch. I will share a little bit about it.

Here's the sample query from Sql Server 2005 Documentation:

USE AdventureWorks
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID;

here is the result :

VendorID Emp1 Emp2 Emp3 Emp4 Emp5
1 4 3 5 4 4
2 4 1 5 5 5
3 4 3 5 4 4
4 4 2 5 5 4
5 5 1 5 5 5

if your database was upgraded from sql 2000 to sql 2005 , then you may set the compatiblel level to 90 to make the feature availeble.

sp_dbcmptlevel [ [ @dbname = ] name ] [ , [ @new_cmptlevel = ] version ]

example : sp_dbcmptlevel 'databasename', '90

remember : if you use pivot features you have to :
1. one column as the value to aggregate
2. one column as the column name . From the example , the column name would be employeeid.
Unfortunately, You have to define it.
I thought i can use

FOR EmployeeID IN
( select employeeid from HumanResources.Employee )

but it doesn't work, that will make my query more fleksible if it's work. I will find other way to make it. May be a little trik for it. I will share it on other post.
3. you are in sql 2005 - > there's must be obsolute.
4. the feature is on. use sp_dbcmplevel to change the level to 90.

How cool isn't it ? Have a try and have a nice day.