PIVOT queries in SQL Server 2005 / 2008

By | December 31, 2008

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, Computer
FROM
(
      SELECT CAST(YEAR(SaleDate) AS VARCHAR(4)) AS SaleDate, SalePrice,
      Product FROM SalesHistory
) AS rf
PIVOT
(
        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