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 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. Swap rows and columns based on aggregation on a column data. The table rotates twice by aggregation, but may not return the actual table rows. The operation can 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.
First, create a table with the following data:
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
To get the SUM of sales for each place, swap the sales and place using the following query:
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