SQL Server Interview Question #175

What is UNPIVOT?

Database Design, Normalization & Advanced SQL Senior Advanced

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;