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