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.
The first query shows the initial data set.
SELECT T.Name, T.Val1, .Val2 FR M (VALUES ('Apple', 'A1', 'A2'), ('Banana', 'B1', 'B2'), ('Cat', 'C1', 'C2') ) AS T(Name, Val1, Val2) ;
This query takes the table-valued constructor from the first query and UNPIVOTS it.
SELECT UNPVT.Name, UNPVT.NEW_COLUMN_NAME, NPVT.EXISTING_COL_NAME FR M (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;
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;
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!