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