16.20 - Example: More Than One Value Column - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Operators and User-Defined Functions

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Administration
Programming Reference
Publication ID
B035-1210-162K
Language
English (United States)

In the following example, there is more than one value column and the COLUMN_LIST takes the form: 'a, x, ..', 'b, y, ..', 'c, z, ...', ... where a, x, b, y, c, z, ... are the names of the columns to be unpivoted into the value columns.

Each individual list, for example, 'a, x, ..', defines which columns are mapped to the value columns for one of the rows of output.

The input table T has columns id, year, jan_sales, jan_expense, feb_sales, feb_expense, ..., dec_sales, dec_expense.

SELECT * from UNPIVOT(
        ON( select * from T)
        USING
            VALUE_COLUMNS('monthly_sales', 'monthly_expense')
            UNPIVOT_COLUMN('month')
            COLUMN_LIST('jan_sales, jan_expense', 'feb_sales,                feb_expense', ..., 'dec_sales, dec_expense')
            COLUMN_ALIAS_LIST('jan', 'feb', ..., 'dec' )
    )X;

The output columns are the following: id, year, month, monthly_sales, monthly_expense.

One row of input, for example:

id   year  jan_sales  jan_exp feb_sales feb_exp...dec_sales dec_exp
123   2012  100        50      200       100  ...  1200      600

generates 12 rows of output:

id  year    month   monthly_sales   monthly_expense
    123 2012    jan     100             50
    123 2012    feb     200             100
    ...
    123 2012    dec     1200            600