Example: Using TD_UNPIVOT or UNPIVOT as a Source to PIVOT

Teradata Vantageā„¢ SQL Functions, Expressions, and Predicates

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

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.

Swap rows and columns within a single query by giving UNPIVOT query as a source to PIVOT. This provides flexibility for a two-dimensional way of interchanging data in a table based on some aggregation on a column.

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.

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