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 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_