15.00 - TD_UNPIVOT - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

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.