16.20 - Example: Naming Columns with the <column_value_list> Values - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Functions, Expressions, and Predicates

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
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