Pivot queries

Posted on Posted in Uncategorized

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

Leave a Reply

Your email address will not be published. Required fields are marked *