Inside Microsoft® SQL Server® 2008: T-SQL Querying

Unpivoting

Unpivoting is the opposite of pivoting—namely, rotating columns to rows. Unpivoting is usually used to normalize data, but it has other applications as well.

Note

Unpivoting is not an exact inverse of pivoting—it won’t necessarily allow you to regenerate source rows that were pivoted. However, for the sake of simplicity, think of it as the opposite of pivoting.

In my examples, I’ll use the PvtCustOrders table, which you create and populate by running the following code:

USE tempdb; IF OBJECT_ID('dbo.PvtCustOrders') IS NOT NULL DROP TABLE dbo.PvtCustOrders; GO SELECT custid, COALESCE([2006], 0) AS [2006], COALESCE([2007], 0) AS [2007], COALESCE([2008], ...