You are currently viewing T-SQL Pivot and Unpivot
Microsoft SQL Server

T-SQL Pivot and Unpivot

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:

Unpivoted
Unpivoted

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:

Pivoted
Pivoted

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

Tomas Lind

Tomas Lind - Consulting services as SQL Server DBA and Database Developer at High Coast Database Solutions AB.

This Post Has One Comment

  1. george

    Nice example with no frills. Very good.

Leave a Reply