SQL Server 2005 TSQL Enhancements: PIVOT and UNPIVOT Operators

We all know the old complex SELECT...CASE way of creating cross tab reports
in SQL 2005 the new PIVOT operator provides a more readable and easy way to create cross tab reports
the general syntax :

SELECT * FROM <table_source>
PIVOT (
aggregate_function ( value_column )
FOR pivot_column IN ( <column_list>)
) table_alias

for example we have a database with these two tables

Emps(EmpID,Name,ManagerID,DeptID)
Depts(DeptID,DeptName)

and we want to create a report that shows for each department the number of employees in each level
so the report should look like this

DeptName	Level1	Level2	Level3
Development Department	1	3	4
Sales Department	1	1	0

first of all we need a query that returns the level of the emplyee we will use a Common Table Expression for this

with Emps_CTE (EmpID,Name,DeptID,Level)
as
(
select EmpID,Name,Emps.DeptID,0 as Level FROM Emps WHERE ManagerID is null
Union All
Select Emps.EmpID,Emps.Name,Emps.DeptID,Level + 1 FROM Emps Join Emps_CTE ON
Emps_CTE.EmpID=Emps.ManagerID
)

Now we have a table that contains the EmpID,Name,DeptID,Level this table will be our <table_source>
and we want to show the number of employees so our aggregate_function will be COUNT and our value_column will be EmpID,
the pivot_column is the column from which unique values will be turned into columns
in our case we want the values of the Level column (0,1,2,..) to appear
as columns so our pivot_column is the Level column

the <column_list> lists the values in the pivot_column that will become the column names of the output table,
the strange thing is that you can't populate the from a query i mean you can't for example say FOR pivot_column IN ( select * FROM other_table)

any way the final query will be

with Emps_CTE (EmpID,Name,DeptID,Level)
as
(
select EmpID,Name,Emps.DeptID,0 as Level FROM Emps WHERE ManagerID is null
Union All
Select Emps.EmpID,Emps.Name,Emps.DeptID,Level + 1 FROM Emps Join Emps_CTE ON
Emps_CTE.EmpID=Emps.ManagerID
)
select DeptName,[0] as FirstLevel,[1] as SecondLevel,[2] as ThirdLevel FROM
(select Emps_CTE.Level,Emps_CTE.DeptID,EmpID,DeptName from Emps_CTE JOIN Depts on Emps_CTE.DeptID=Depts.DeptID) p
Pivot
(Count(EmpID) For Level in([0],[1],[2])) as pvt