The PIVOT operator available in SQL Server 2005 is used to generate the Cross-tab results Consider this example select * from ( select Year ( OrderDate ) as pivot_col , e . lastname , o . OrderDate FROM northwind .. Employees as e INNER JOIN northwind .. Orders as o ON ( e . EmployeeID = o . EmployeeID ) ) as t pivot ( Count ( OrderDate ) for pivot_col in ( [1996] , [1997] ) ) as p which shows total orders of each employees for years 1996 and 1997 What if we want to have this for all the years available...(
read more)
Read the complete post at http://sqlserver-qa.net/blogs/t-sql/archive/2008/08/27/4809.aspx