PIVOT and UNPIVOT quick reference

The T-SQL relational operators PIVOT and UNPIVOT are super useful, but I use them *just* infrequently enough where I can never remember the exact syntax from memory. I decided to create this post for a quick reference for myself, and maybe someone will also find it handy.

First off, what do PIVOT and UNPIVOT do? PIVOT will turn row values into columns, and UNPIVOT can turn columns into row values. It’s easier to visualize by following the examples below. I wanted the examples to be a quick copy/paste/run into SQL Server Management Studio, so the source table is a hard-coded inline table from a VALUES table constructor.

Initial Table

The first query shows the initial data set.

SELECT
	T.Name,
	T.Val1,
	T.Val2
FROM
	(VALUES 
		('Apple', 'A1', 'A2'),
		('Banana', 'B1', 'B2'),
		('Cat', 'C1', 'C2')
	) AS T(Name, Val1, Val2)
;

Source Table

UNPIVOT

This query takes the table-valued constructor from the first query and UNPIVOTS it.

SELECT
	UNPVT.Name,
	UNPVT.NEW_COLUMN_NAME,
	UNPVT.EXISTING_COL_NAME
FROM
	(VALUES 
		('Apple', 'A1', 'A2'),
		('Banana', 'B1', 'B2'),
		('Cat', 'C1', 'C2')
	) AS T(Name, Val1, Val2)
UNPIVOT 
	(NEW_COLUMN_NAME FOR EXISTING_COL_NAME IN (Val1, Val2)) AS UNPVT;

Results of UNPIVOT

PIVOT

This query demonstrates PIVOT. It uses a hard-coded equivalent of the result set of query #2 and PIVOTS it back to the original form (embedding an UNPIVOT of the VALUES table-constructor started to look a little too busy and took the focus away from the PIVOT syntax). Note that an aggregate operator is needed, and the FOR clause matches the FOR clause in the UNPIVOT example.

SELECT
	 Pvt.Name,
	 Pvt.Val1,
	 Pvt.Val2
FROM 
	(VALUES 
	('Apple', 'A1', 'Val1'),
	('Apple', 'A2', 'Val2'),
	('Banana', 'B1', 'Val1'),
	('Banana', 'B2', 'Val2'),
	('Cat', 'C1', 'Val1'),
	('Cat', 'C2', 'Val2')) 
	AS T(Name, NEW_COLUMN_NAME, EXISTING_COL_NAME)
PIVOT (
	MIN(NEW_COLUMN_NAME) FOR EXISTING_COL_NAME IN ([Val1], [Val2])
) as Pvt;

Results of PIVOT the results of UNPIVOT

Conclusion

Official documentation can be found (among other places) at https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot. Happy pivoting!

PIVOT and UNPIVOT quick reference

Leave a Reply

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