Posted in

T-SQL Pivot and Unpivot

Microsoft SQL Server
Microsoft SQL Server

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 - Consulting services as SQL Server DBA and Database Developer at High Coast Database Solutions AB.

One thought on “T-SQL Pivot and Unpivot

Leave a Reply

Discover more from Tomas Lind

Subscribe now to keep reading and get access to the full archive.

Continue reading