Posts

Showing posts from May, 2005

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

DeptNameLevel1Level2Level3
Development Department134
Sales Department110


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.ManagerI…