Example: Subquery in PIVOT IN-List

Teradata Vantageā„¢ SQL Functions, Expressions, and Predicates

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

This is an example of having a subquery in PIVOT IN-list.

Table s1 is defined as:
CREATE TABLE s1(yr INTEGER, mon VARCHAR (5), sales INTEGER);
CREATE TABLE s2(yr INTEGER, mon VARCHAR (5), sales INTEGER);
The table contains:
SELECT * FROM s1;
yr      mon      sales
-----   ---      -----
2001    Jan       100
2003    Jan       300
2002    Jan       150
2001    Feb       110
2003    Feb       310
2002    Feb       200
2001    Mar       120
2002    Mar       250

SELECT * FROM s2;
 yr     mon      sales
-----  -----    -------
2001    Jan       100
2002    Mar       250
2003    Feb       310
The table as a source to a PIVOT query having a subquery in PIVOT IN-list:
SELECT * FROM s1 PIVOT (SUM (sales) FOR mon in (SELECT mon FROM s2)) dt;
The output pivoted table:
*** Query completed. 3 rows found. 4 columns returned.
 *** Total elapsed time was 1 second.

         yr        'Feb'        'Jan'        'Mar'
-----------  -----------  -----------  -----------
       2001          110          100          120
       2003          310          300           ?
       2002          200          150          250