TD_UNPIVOT - Teradata Database

TD_UNPIVOT

Purpose  

Transforms table columns into rows.

Syntax  

where:

Syntax element …	Specifies …
TD_SYSFNLIB	the name of the database where the function is located.
tableName	the name of a table with the input data for the function.
query_expression	the SELECT statement with input data for the function.
VALUE _COLUMNS	the destination column or columns for the unpivotted data.
UNPIVOT_COLUMN	the name of the output column identifying the unpivotted data in each row of the value column or columns.
COLUMN_LIST	the input column data for the unpivotting operation.
COLUMN_ALIAS_LIST	alternate names for the values in the unpivotted column.
INCLUDE_NULLS	whether or not to include nulls in the transformation Valid values are: ‘Yes’ ‘No’ (default)

TD_SYSFNLIB

Name of the database where the function is located.

ANSI Compliance

This is a Teradata extension to the ANSI SQL:2011 standard.

Data Type

The data types of all source columns that are mapped into the same value column must be compatible. For the purposes of TD_UNPIVOT, two columns are compatible if they are both numeric, both CHAR or VARCHAR, both BYTE or VARBYTE, or both identical.

Authorization

You must have EXECUTE FUNCTION privileges on the function or on the database containing the function.

Example  

In the following example, 12 columns (shown in COLUMN_LIST) are unpivotted 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 

Example  

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 unpivotted 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

Usage Notes for Examples

Columns in table T that are not included in any of the column lists are referred to as "copy columns," and are included in every row of the output.

Value column names and the UNPIVOT column name must not exist in table T.

The names in column_list_value must exist in table T.

The number of VALUE_COLUMNS must be equal to the number of columns in each column list. One column list defines which source columns get mapped to the value columns for one row of output.

Column alias list values are names that are used for the UNPIVOT_COLUMN to indicate which source column(s) data is in the value columns for that row.

If specified, the number of alias names must be equal to the number of column lists.

If not specified, column alias names default to the names of the columns in the column list, concatenated with an '_'.

The UNPIVOT_COLUMN is a VARCHAR(1000).

If INCLUDE_NULLS = 'yes', there will be exactly n rows of output for every row of input, where n is the number of column lists.

If INCLUDE_NULLS = 'no', then there will be at most n rows of output for every row of input, where n is the number of column lists. Any row with NULLS for every value column is eliminated.