Kasper de Jonge PowerPivot Blog

SQL Server PIVOT operator allows you to rotate row level data into tabular data without the use of the CASE statement. With the PIVOT operator in SQL Server, the CASE statement and GROUP BY statements are no longer necessary. (Every PIVOT query involves an aggregation of some type, so you can omit the GROUP BY statement.) The PIVOT operator provides the same functionality that we tried to achieve with the CASE statement query, but you can achieve it through less code, and it is a bit more pleasing on your eyes.

Small sample with case:

SELECT      CAST(YEAR(SaleDate) AS VARCHAR(4)) AS SaleDate,      SUM(CASE WHEN Product = 'BigScreen' THEN SalePrice END) AS BigScreen,       SUM(CASE WHEN Product = 'PoolTable' THEN SalePrice END) AS PoolTable,       SUM(CASE WHEN Product = 'Computer' THEN SalePrice END) AS Computer       FROM       SalesHistory
GROUP BY       CAST(YEAR(SaleDate) AS VARCHAR(4))

 

Same result with the PIVOT operator

SELECT       SaleDate, BigScreen, PoolTable, ComputerFROM(      SELECT CAST(YEAR(SaleDate) AS VARCHAR(4)) AS SaleDate, SalePrice, 

      Product FROM SalesHistory ) AS rfPIVOT(        SUM(SalePrice) FOR Product IN(BigScreen, PoolTable, Computer) ) AS p

Very nice to use in an ETL process.

From http://articles.techrepublic.com.com/5100-10878_11-6143761.html