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

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