Blog

A simple demonstration of the Pivot and Unpivot functions in T-SQL.

First, let’s create a table containing budgets for departments and months:

CREATE TABLE dbo.PivotDemo
	(
		DepartmentId INT NOT NULL
		,BudgetMonth CHAR(3) NOT NULL
		,BudgetAmount INT NOT NULL
	)
GO

INSERT dbo.PivotDemo VALUES
	(1, 'Jan', 4352)
	,(1, 'Feb', 3244)
	,(1, 'Mar', 5466)
	,(1, 'Apr', 9787)
	,(1, 'May', 4664)
	,(1, 'Jun', 4568)
	,(1, 'Jul', 4545)
	,(1, 'Aug', 7445)
	,(1, 'Sep', 7853)
	,(1, 'Oct', 3245)
	,(1, 'Nov', 8544)
	,(1, 'Dec', 3455)
	,(2, 'Jan', 5673)
	,(2, 'Feb', 4545)
	,(2, 'Mar', 4534)
	,(2, 'Apr', 9787)
	,(2, 'May', 5645)
	,(2, 'Jun', 3456)
	,(2, 'Jul', 4532)
	,(2, 'Aug', 4556)
	,(2, 'Sep', 5677)
	,(2, 'Oct', 4566)
	,(2, 'Nov', 5734)
	,(2, 'Dec', 6534)
	,(3, 'Jan', 5653)
	,(3, 'Feb', 6734)
	,(3, 'Mar', 6633)
	,(3, 'Apr', 9787)
	,(3, 'May', 3245)
	,(3, 'Jun', 4533)
	,(3, 'Jul', 5435)
	,(3, 'Aug', 5345)
	,(3, 'Sep', 6535)
	,(3, 'Oct', 6436)
	,(3, 'Nov', 7634)
	,(3, 'Dec', 7787)
	,(4, 'Jan', 4666)
	,(4, 'Feb', 7757)
	,(4, 'Mar', 9787)
	,(4, 'Apr', 9787)
	,(4, 'May', 7567)
	,(4, 'Jun', 4564)
	,(4, 'Jul', 5356)
	,(4, 'Aug', 8656)
	,(4, 'Sep', 7764)
	,(4, 'Oct', 5675)
	,(4, 'Nov', 5666)
	,(4, 'Dec', 4566)
GO

The table now has the following data:

Pivot

The information in the table created above can be pivoted to rotate the months from rows into columns:

SELECT * FROM
	(
		SELECT
			DepartmentId
			,BudgetMonth
			,BudgetAmount
		FROM
			dbo.PivotDemo
	) AS T
PIVOT
	(
		SUM(BudgetAmount)
		FOR BudgetMonth IN (Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec)
	) AS p

The pivoted result is in this case much easier to read than the original table:

Unpivot

When you have pivoted data that you need to transform into a traditional table format, the Unpivot function can turn columns into rows.

First, let’s create a table containing the pivoted data:

SELECT * INTO dbo.UnpivotDemo FROM
	(
		SELECT
			DepartmentId
			,BudgetMonth
			,BudgetAmount
		FROM
			dbo.PivotDemo
	) AS T
PIVOT
	(
		SUM(BudgetAmount)
		FOR BudgetMonth IN (Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec)
	) AS p

To unpivot this data, the syntax is similar to the pivot syntax:

SELECT * FROM
	(
		SELECT
			DepartmentId
			,Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
		FROM
			dbo.UnpivotDemo
	) AS P
UNPIVOT
	(
		BudgetAmount
		FOR BudgetMonth IN (Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec)
	) AS T