The following example of a view as a PIVOT source.
Assume a view, v1, is defined on the table s1:
CREATE TABLE s1(yr INTEGER, mon VARCHAR(4), sales INTEGER);
sel * from s1;
sel * from s1;
*** Query completed. 8 rows found. 3 columns returned.
*** Total elapsed time was 1 second.
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
CREATE VIEW V1 AS select yr,sales from s1;
*** View has been created.
*** Total elapsed time was 1 second.
sel * from v1;
select * from v1;
*** Query completed. 8 rows found. 2 columns returned.
*** Total elapsed time was 1 second.
yr sales
----------- -----------
2002 150
2003 300
2002 200
2003 310
2002 250
2001 100
2001 110
2001 120
The following query generates sales report with respect to each year on view V1:
SELECT *
FROM v1 PIVOT (SUM(sales) FOR yr IN (2001,2002,2003)) tmp;
*** Query completed. One row found. 3 columns returned.
*** Total elapsed time was 1 second.
2001 2002 2003
----------- ----------- -----------
330 600 610