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:
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
CAST(YEAR(SaleDate) AS VARCHAR(4))
Same result with the PIVOT operator
SaleDate, BigScreen, PoolTable, Computer
SELECT CAST(YEAR(SaleDate) AS VARCHAR(4)) AS SaleDate, SalePrice,Product FROM SalesHistory
) AS rf
SUM(SalePrice) FOR Product IN(BigScreen, PoolTable, Computer)
) AS p
Very nice to use in an ETL process.