Example: Normalizing the UNPIVOT Operation - Analytics Database - Teradata Vantage

This example showcases using UNPIVOT to capture elaborate data of a base table, star1p. The data is spread over many columns into a compact table with an optimal number of columns and no data loss.

SELECT *
FROM star1p UNPIVOT (measure_value  FOR  yr_qtr_measure IN
(Q101Sales, Q201Sales, Q301Sales,Q101Cogs, Q201Cogs, Q301Cogs)) Tmp;

Result:

country  state  yr_qtr_measure  measure_value
-------  -----  --------------  -------------
Canada   BC     Q301Cogs                    0
Canada   BC     Q301Sales                  10
Canada   ON     Q201Cogs                    0
Canada   ON     Q201Sales                  10
USA      CA     Q101Cogs                   15
USA      CA     Q101Sales                  30
USA      CA     Q201Cogs                   20
USA      CA     Q201Sales                  50
USA      NY     Q101Cogs                   25
USA      NY     Q101Sales                  45