PIVOT Function Examples | Teradata Vantage - Example: Alias Names Contained in the IN List - Analytics Database - Teradata Vantage

SQL Functions, Expressions, and Predicates

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-01-12
dita:mapPath
obm1628111499646.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
kby1472250656485
lifecycle
latest
Product Category
Teradata Vantage™

This example uses the star1 table, with the following definition and contents:

CREATE TABLE star1(country VARCHAR(20),state VARCHAR(10), yr INTEGER,qtr VARCHAR(3),sales INTEGER,cogs INTEGER);

SELECT * FROM star1;
country  state           yr  qtr        sales         cogs
-------  -----  -----------  ---  -----------  -----------
USA      CA            2001  Q1            30           15
Canada   ON            2001  Q2            10            0
Canada   BC            2001  Q3            10            0
USA      NY            2001  Q1            45           25
USA      CA            2001  Q2            50           20

In this example, the IN list contains alias names. The alias names are concatenated with the alias names specified by the aggregate functions to build the column names of the output pivoted table.

SELECT *
FROM star1 PIVOT (
                   SUM(sales) as ss1, SUM(cogs) as sc FOR qtr                                                                                               
                                       IN (‘Q1’ AS Quarter1,                                                                                                     
                                           ‘Q2’ AS Quarter2, 
                                           ‘Q3’ AS Quarter3)
                                          )Tmp;

The output is re-written as an equivalent SELECT query using CASE statements:

SELECT *  FROM  (SELECT country ,state ,yr ,
SUM(CASE WHEN qtr =  'Q1' THEN sales ELSE NULL END )AS  Quarter1_ss1,
SUM(CASE WHEN qtr =  'Q1' THEN (cogs) ELSE NULL END )AS Quarter1_sc,
SUM(CASE WHEN qtr =  'Q2' THEN (sales) ELSE NULL END)AS Quarter2_ss1,
SUM(CASE WHEN qtr =  'Q2' THEN (cogs) ELSE NULL  END)AS Quarter2_sc,
SUM(CASE WHEN qtr =  'Q3' THEN (sales) ELSE NULL END)AS Quarter3_ss1,
SUM(CASE WHEN qtr =  'Q3' THEN (cogs) ELSE NULL END)AS Quarter3_sc
FROM star1 GROUP BY country ,state ,yr ) Tmp ;

Output pivoted table:

country state yr   Quarter1_ss1 Quarter1_sc Quarter2_ss1 Quarter2_sc Quarter3_ssl Quarter3_sc
------- ---- ----  ------------ ----------- ------------ ----------- ------------ -----------
USA     CA  2001             30          15           50          20            ?           ?
USA     NY  2001             45          25            ?           ?            ?           ?
Canada  ON  2001              ?           ?           10           0            ?           ?
Canada  BC  2001              ?           ?            ?           ?           10           0