Pivot Tables

Suppose you have the following table named TRANSACTIONS, which has two columns, ITEM_ID and PRICE. Below is an example of the table:

ITEM_ID	PRICE
1	$5.00
1	$6.00
2	$5.00
3	$7.00
3	$2.00
4	$3.00

A user would like to see this data pivoted, where each distinct ITEM_ID becomes a column header, and the data becomes the sum of PRICE grouped by ITEM_ID. Below is the desired result:

1	2	3	4
$11.00	$5.00	$9.00	$3.00

You could probably achieve this result by writing four separate queries then joining the individual results together but there is perhaps a better way to do it. The trick is to use the CASE keyword in combination with the SUM aggregate function. Here’s how to do it:

SELECT
sum(case when ITEM_ID = 1 then PRICE else 0 end) AS [1],
sum(case when ITEM_ID = 2 then PRICE else 0 end) AS [2],
sum(case when ITEM_ID = 3 then PRICE else 0 end) AS [3],
sum(case when ITEM_ID = 4 then PRICE else 0 end) AS [4]
FROM TRANSACTIONS