PIVOT supports UNPIVOT query or the TD_UNPIVOT function as a source for the PIVOT operator.
PIVOT/ UNPIVOT uses a single dimensional method to interchange data, such as converting rows to columns, or columns to rows, based on some aggregation on a column data.
To change data with a two-dimensional method, aggregate data on a column, and then interchange the rows and columns twice. In this case, swap rows and columns based on some aggregation on a column data. The table rotates twice by some aggregation, but might not return the actual table rows. It could introduce new rows where data is missing, or eliminate rows if data is aggregated in the process.
Two-dimensional uses PIVOT as source to the UNPIVOT query, or UNPIVOT as a source to a PIVOT query. Using PIVOT as source to an UNPIVOT query is complex when writing the SQL, whereas using UNPIVOT as a source to PIVOT query is easier.
CREATE TABLE t1 (place CHAR(5), sales1 INTEGER, sales2 INTEGER,
sales3 INTEGER, sales4 INTEGER, sales5 INTEGER)
PRIMARY INDEX ( place );
place sales1 sales2 sales3 sales4 sales5
----- --------- -------- -------- -------- --------
Hyd 110 100 1000 1100 500
Che 120 200 2000 1200 600
Kol 150 500 5000 1500 900
Mee 140 400 4000 1400 800
Pun 130 300 3000 1300 700
SELECT * from (SELECT * from t1
UNPIVOT(saleval
for sales in (sales1, sales2, sales3,
sales4, sales5))dt1)dt2
PIVOT(SUM(saleval)
for place in ('hyd','Che','pun',
'mee','kol'))dt3;
The results for using UNPIVOT as the source:sales Hyd Che Pun Mee Kol ----- -------- -------- -------- -------- -------- sales1 110 120 130 140 150 sales2 100 200 300 400 500 sales3 1000 2000 3000 4000 5000 sales4 1100 1200 1300 1400 1500 sales5 500 600 700 800 900