SQL Server Interview Question #175

What is UNPIVOT?

Database Design, Normalization & Advanced SQL Senior Advanced

Quick Interview Answer

UNPIVOT performs the conceptual reverse of PIVOT by transforming multiple columns into rows. It is useful when data has been stored or received in a cross-tab structure but needs to be processed in a more relational row-based format. CROSS APPLY with VALUES can also be a flexible alternative for unpivoting in many SQL Server queries.

Detailed Explanation

UNPIVOT performs the conceptual reverse of PIVOT by transforming multiple columns into rows.

It is useful when data has been stored or received in a cross-tab structure but needs to be processed in a more relational row-based format. CROSS APPLY with VALUES can also be a flexible alternative for unpivoting in many SQL Server queries.

Code Example

SELECT ProductId, PriceType, Price
FROM
(
    SELECT ProductId, RetailPrice, SalePrice
    FROM dbo.ProductPrices
) AS p
UNPIVOT
(
    Price FOR PriceType IN (RetailPrice, SalePrice)
) AS u;