17.10 - Example: Using UNPIVOT with the INCLUDE NULLS Clause - Advanced SQL Engine - Teradata Database

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

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1145-171K
Language
English (United States)

In this example, there are some rows with nulls in the sales and cogs columns. The rows are included in the output when using the INCLUDE NULLS clause.

SELECT *
FROM star1p UNPIVOT INCLUDE NULLS ((sales,cogs)  FOR  yr_qtr IN
((Q101Sales, Q101Cogs) AS 'Q101', (Q201Sales, Q201Cogs) AS 'Q201', (Q301Sales, Q301Cogs) AS 'Q301')) Tmp;

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