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

SQL Operators and User-Defined Functions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2023-04-27
dita:mapPath
xwv1596137968859.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1210
lifecycle
previous
Product Category
Teradata Vantage™

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