Attribute Unpivoting via Cartesian Product and CASE

Snippet

Attribute Unpivoting via Cartesian Product and CASE

Unpivoting transforms multiple attribute columns into a standardized row-based format. This ANSI-compliant method uses a CROSS JOIN to a virtual table of labels to duplicate each source row, then uses a CASE expression to select the corresponding column value for each label.

snippet.sql
sql
SELECT t.id, labels.name, 
       CASE labels.name 
           WHEN 'Price' THEN t.price 
           WHEN 'Tax' THEN t.tax 
       END AS value 
FROM items t 
CROSS JOIN (SELECT 'Price' AS name UNION ALL SELECT 'Tax') AS labels;

Breakdown

CROSS JOIN (SELECT 'Price' AS name ...)

Multiplies each source row by the number of attributes being unpivoted.

CASE labels.name WHEN 'Price' THEN t.price ...

Maps the virtual label back to the actual column value from the source table.

END AS value

Projects the resulting attribute value into a single column.

Previous snippet Next snippet

From your library

Attribute Unpivoting via Cartesian Product and CASE

Related