16.20 - Example: Twelve Columns Unpivoted to One - 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, 12 columns (shown in COLUMN_LIST) are unpivoted to 1 column with 12 rows.

The input table T in the query_expression (in the SELECT statement below) has the following columns: id, year, jan_sales, feb_sales, ..., dec_sales

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

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

One row of input, for example:

     id
      year
    jan_sales
   feb_sales
   ... dec_sales
     123     2012    100         200         ... 1200

generates 12 rows of output:

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

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