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;
Result:
*** 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;
Result:
*** View has been created. *** Total elapsed time was 1 second.
sel * from v1; select * from v1;
Result:
*** 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;
Result:
*** Query completed. One row found. 3 columns returned. *** Total elapsed time was 1 second. 2001 2002 2003 ----------- ----------- ----------- 330 600 610