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)
;
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;
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;
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!
