sql / expert
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
1
2
3
4
5
6
7
SELECT t.id, labels.name,CASE labels.nameWHEN 'Price' THEN t.priceWHEN 'Tax' THEN t.taxEND AS valueFROM items tCROSS JOIN (SELECT 'Price' AS name UNION ALL SELECT 'Tax') AS labels;
Breakdown
1
CROSS JOIN (SELECT 'Price' AS name ...)
Multiplies each source row by the number of attributes being unpivoted.
2
CASE labels.name WHEN 'Price' THEN t.price ...
Maps the virtual label back to the actual column value from the source table.
3
END AS value
Projects the resulting attribute value into a single column.