17.10 - Example: Normalizing the UNPIVOT Operation - Advanced SQL Engine - Teradata Database

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

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

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.

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