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

Teradata Vantage™ - SQL Functions, Expressions, and Predicates

prodname
Advanced SQL Engine
Teradata Database
vrm_release
16.20
created_date
March 2019
category
Programming Reference
featnum
B035-1145-162K

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