16.20 - Example: Naming Columns with the <column_value_list> Values - Advanced SQL Engine - Teradata Database

In this example, the SELECT statement does not specify the names to use for columns explicitly. The names of the columns are built internally by adding the aggregated column name to the <column_value_list> values.

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

This is re-written as an equivalent SELECT query that uses CASE statements:

SELECT *  FROM  (SELECT country ,state ,
SUM(CASE WHEN yr =  2001 AND  qtr =  'Q1' THEN sales ELSE NULL END) AS "2001_'Q1'_ss1" ,
SUM(CASE WHEN yr =  2001 AND  qtr =  'Q1' THEN cogs ELSE NULL END) AS "2001_'Q1'_sc",
SUM(CASE WHEN yr =  2001 AND  qtr =  'Q2' THEN sales ELSE NULL END) AS "2001_'Q2'_ss1" ,
SUM(CASE WHEN yr =  2001 AND  qtr =  'Q2' THEN cogs ELSE NULL END) AS "2001_'Q2'_sc",
SUM(CASE WHEN yr =  2001 AND  qtr =  'Q3' THEN sales ELSE NULL END) AS "2001_'Q3'_ss1",
SUM(CASE WHEN yr =  2001 AND  qtr =  'Q3' THEN cogs ELSE NULL END) AS "2001_'Q3'_sc"
FROM star1 GROUP BY country ,state ) Tmp ;

Output pivoted table:

country state 2001_'Q1'_ss1 2001'_'Q1'_sc 2001_'Q2'_ss1 2001_'Q2'_sc 2001_'Q3'_ssl 2001_'Q3'_sc
------- ----  ------------- ------------- ------------- ------------ ------------- ------------
USA     CA               30            15            50           20            ?             ?
USA     NY               45            25             ?            ?            ?             ?
Canada  ON                ?             ?            10            0            ?             ?
Canada  BC                ?             ?             ?            ?           10             0