Example: Subquery in PIVOT IN List

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

Example: Subquery in PIVOT IN List - Teradata Vantage

Teradata® VantageCloud Lake