UNPIVOT Function Examples | Teradata Vantage - 17.10 - Example: Unpivoted Sales and Cogs Columns - 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)

In this example, the sales and cogs columns are unpivoted.

FROM star1p UNPIVOT ((sales,cogs)  FOR  yr_qtr 
                          IN ((Q101Sales, Q101Cogs) AS ‘Q101’, 
                              (Q201Sales, Q201Cogs) AS ‘Q201’,
                              (Q301Sales, Q301Cogs) AS ‘Q301’)) Tmp;

The output for the unpivoted table:

country  state  yr_qtr        sales         cogs
-------  -----  ------  -----------  -----------
Canada   ON     Q201             10            0
Canada   ON     Q301             10            0
USA      NY     Q101             45           25
USA      CA     Q101             30           15
USA      CA     Q201             50           20

Note that a pivot combined with a matching unpivot may introduce rows with NULL values. It is possible to unpivot just the ‘yr’ column.