Pivot queries

I stumbled across the PIVOT keyword while poking through the dynamic management tables in SQL trying to track down some sproc recompiles. Pivots are great for denormalizing data for easy reporting. OLTP schema is great for quick insert/update/deletes but piecing data together into something useful can be a pain.

A simple budget table that tracks a budget item type and a budget amount (rather than having a separate amount column for each type of item) is a good example of where this is useful...

DECLARE @BudgetItems TABLE
(CustId int, BudgetItemType varchar(20), BudgetAmount money)

--Customer 1 is living the life
INSERT @BudgetItems (CustId, BudgetItemType, BudgetAmount) VALUES (1, 'Mortgage', 1000)
INSERT @BudgetItems (CustId, BudgetItemType, BudgetAmount) VALUES (1, 'Salary', 6000)
INSERT @BudgetItems (CustId, BudgetItemType, BudgetAmount) VALUES (1, 'Food', 140)

--Customer 2 has no income
INSERT @BudgetItems (CustId, BudgetItemType, BudgetAmount) VALUES (2, 'Salary', 400)
INSERT @BudgetItems (CustId, BudgetItemType, BudgetAmount) VALUES (2, 'Food', 340)

The table ends up looking like:

SELECT * FROM @BudgetItems

CustId BudgetItemType BudgetAmount
------ -------------- ------------
1      Mortgage       1000.00
1      Salary         6000.00
1      Food           140.00
2      Salary         400.00
2      Food           340.00

Again, very easy to ins/upd/del, but difficult to get a single client's budget into a row for something like binding a report to it as a datasource. Pivoting the table is an easy (if somewhat syntax-clumsy) solution to this:

SELECT pivotTable.*
FROM (SELECT CustId, BudgetItemType, BudgetAmount FROM @BudgetItems) AS source
PIVOT ( SUM(source.BudgetAmount) FOR BudgetItemType IN (Mortgage, Salary, Food) ) AS pivotTable

CustId Salary  Mortgage Food
------ ------- -------- ------
1      6000.00 1000.00  140.00
2      400.00  NULL     340.00