Example: Normalizing the UNPIVOT Operation

Teradata Vantageā„¢ SQL Functions, Expressions, and Predicates

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

This example showcases using UNPIVOT to capture elaborate data of a base table (star1p, in this case). 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;
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