Transposition Table Definition

You can use a Transposition Table Definition to restructure datasets by rotating rows into columns (PIVOT) or columns into rows (UNPIVOT).

Syntax


transposition_table_definition ::= DEFINE [export_specification] [transposition_table_type] DATASET table_name
                                   { transposition_code_block | pivot_code_block_load | unpivot_code_block_load }
                                   END

transposition_table_type ::= VERSIONED

For more information, see Export Specification.

Common Elements 

transposition_rowsource_specification ::= ROWSOURCE { transposition_table_source | inline_source };

transposition_table_source ::= table_reference [ row_filtering_conditional ]

segment ::= WITHIN table_name [EXCLUDE] column_list;

Transposition Code Block

The transposition code block contains either a PIVOT or UNPIVOT block, which determines how the data is transformed. 
transposition_code_block ::= pivot_code_block | unpivot_code_block
See:

Pivot Definition


pivot_code_block ::=  
                    transposition_rowsource_specification
                    pivot_section
                    [ column_mapping_assignment ]...
                    [ primary_key_specification ]

Note:

You're allowed to only map columns for columns generated from PIVOT section. 
pivot_section ::= PIVOT
                                            '('
                                                [ segment ]
                                                pivot_transposition...
                                            ')'

pivot_transposition ::= THIS [ column_list ] '=' { pivot_function_list | pivot_scalar_list }
                        FOR { table_name.column_name IN constant_value_expression | table_column_tuple IN constant_value_tuple_expression }

pivot_function_list ::= '[' aggregate_function [ column_prefix ] [, aggregate_function [ column_prefix ] ] ... ']'

pivot_scalar_list ::= '[' column_name [ column_prefix ] [, column_name [ column_prefix ] ] ... ']'
column_prefix ::= '-' COLPREFIX string

table_column_tuple ::= '(' table_name.column_name [, table_name.column_name] ... ')'

constant_value_expression ::= '(' constant_value [, constant_value] ... ')'

constant_value_tuple_expression ::= '(' constant_value_tuple [, constant_value_tuple] ... ')'
constant_value_tuple ::= '(' constant_value, constant_value [, constant_value] ... ')'

Note:

  • If a WITHIN statement is skipped, all the columns that aren't used in transpositions will be used to partition (group) the data.
  • Within the PIVOT call, transpositions could be aggregated or scalar. If you use aggregation, then all transposition statements must have aggregation.
  • If column names on the left side aren't explicitly mentioned, -COLPREFIX property is mandatory for aggregation-based transpositions and optional for scalar transpositions.
  • ROWSOURCE can't contain joins or unions but it can contain filters.
  • Other transformations (except aggregations) are allowed after the PIVOT block and only by using the columns from PIVOT section.
  • No GROUP BY is allowed in the dataset.
  • Primary key is optional. If optional, the within clause columns are considered as primary key. Primary key can be specified, especially for excluding functionally-dependent columns.

Examples

Calculate average income for each month using country and city. 
DEFINE VERSIONED DATASET DW_CITY_PIVOT
    ROWSOURCE CITIES;
 
    PIVOT
    (   
        /* ------------------------- SPECIFY PARTITION -------------------------- */
        WITHIN CITIES[COUNTRY, CITY, CITY_CODE];
 
        /* ------------------------SPECIFY TRANSPOSITIONS ----------------------- */
        // Provide target column names on LHS for each of the month values
        THIS[AV_INC_JAN, AV_INC_FEB, AV_INC_MAR] = AVG(CITIES.INCOME) FOR CITIES.MONTH IN ('Jan', 'Feb', 'Mar');
    );
  
    PRIMARYKEY [COUNTRY, CITY];
END
Calculate total population and average population for each year using country and city. 
DEFINE VERSIONED DATASET DW_CITY_PIVOT
    ROWSOURCE CITIES;
      
    PIVOT
    (
        WITHIN CITIES[COUNTRY, CITY, CITY_CODE];
 
        // Target column names are generated using -COLPREFIX property. The YEAR values are suffixed to -COLPREFIX using underscore(_).         
        THIS = [ SUM(CITIES.POPULATION) -COLPREFIX 'SUM_POP', AVG(CITIES.POPULATION) -COLPREFIX 'AVG_POP' ]
                      FOR CITIES.YEAR IN (2000, 2010, 2020);
 
         // E.g. Generated columns SUM_POP_2000, AVG_POP_2000, SUM_POP_2010 , ...
    );
 
    THIS[SUM_CHANGE_2010_2020] = THIS.SUM_POP_2000/THIS.SUM_POP_2010;
 
    PRIMARYKEY [COUNTRY, CITY];
END
Calculate total population for each (year, month) combination using country and city. 
DEFINE VERSIONED DATASET DW_CITY_PIVOT
    ROWSOURCE CITIES;

    PIVOT
    (
        WITHIN CITIES[COUNTRY, CITY, CITY_CODE]; 
 
        // Multiple column combinations.
        THIS = SUM(CITIES.POPULATION) -COLPREFIX 'SUM_POP'
                    FOR (CITIES.YEAR, CITIES.MONTH) IN (
                                                          (2000,'Jan'), (2000,'Feb'), (2000,'Mar'),
                                                          (2010,'Feb'), (2010,'Mar'),
                                                          (2020,'Jan'), (2020,'Feb'), (2020,'Mar')
                                                       );
 
       // Column name examples: SUM_POP_2000_JAN, SUM_POP_2000_FEB
    );
  
    PRIMARYKEY [COUNTRY, CITY];
END
Non-aggregation pivot 
DEFINE VERSIONED DATASET DW_CUSTOMERS_PIVOT 
    ROWSOURCE CUSTOMERS;

    PIVOT
    (
        WITHIN CUSTOMERS[ID];

        // For non-aggregation pivot, -COLPREFIX is optional.
        // In this case, use the values from IN clause as column names.
       THIS = CUSTOMERS.AttributeValue FOR CUSTOMERS.Attribute IN ('FirstName', 'LastName', 'DOB');
    );                   

    PRIMARYKEY [ID];                    
END
Combine all PIVOTs in a dataset 
DEFINE VERSIONED DATASET DW_CITY_PIVOT
    ROWSOURCE CITIES;

    PIVOT
    (
        /* ------------------------- SPECIFY PARTITION -------------------------- */
        WITHIN CITIES[COUNTRY, CITY, CITY_CODE];
        // or WITHIN ALL;
 
 
        /* ------------------------SPECIFY TRANSPOSITIONS ----------------------- */
        // Provide target column names on LHS for each of the month values
        THIS[AV_INC_JAN, AV_INC_FEB, AV_INC_MAR] = AVG(CITIES.INCOME) FOR CITIES.MONTH IN ('Jan', 'Feb', 'Mar'); 
 
        THIS =  [ SUM(CITIES.POPULATION) -COLPREFIX 'SUM_POP',  AVG(CITIES.POPULATION) -COLPREFIX 'AVG_POP' ] FOR CITIES.YEAR IN (2000, 2010, 2020);
 
        // Multiple column combinations
        THIS = [SUM(CITIES.POPULATION) -COLPREFIX 'SUM_POP']
                   FOR (CITIES.YEAR, CITIES.MONTH) IN (
                                                       (2000,'Jan'), (2000,'Feb'), (2000,'Mar'),
                                                       (2010,'Feb'), (2010,'Mar'),
                                                       (2020,'Jan'), (2020,'Feb'), (2020,'Mar')
                                                      );
    );
 
    THIS[SUM_CHANGE_2010_2020] = THIS.SUM_POP_2000/THIS.SUM_POP_2010;
  
    PRIMARYKEY [COUNTRY, CITY];
END

Unpivot Definition


unpivot_code_block ::= transposition_rowsource_specification
                       unpivot_section
                       [ column_mapping_assignment ] ...
                       primary_key_specification

Note:

You're allowed to assign columns only for columns generated from the UNPIVOT section. 

unpivot_section ::= UNPIVOT [ INCLUDE NULLS ]
                    '('
                        [ segment ]
                        unpivot_transposition ...
                    ')';

unpivot_transposition ::= THIS column_tuple_list '=' table_name column_list ;
column_tuple_list ::= '[' column_tuple [, column_tuple ]... ']'
column_tuple ::= '(' column_key_value [, column_key_value ]... , identifier ')'
column_key_value ::= identifier ':' constant_value

Note:

  • The WITHIN statement is optional. If it's skipped, all remaining columns that aren't used in transpositions must be used in partition.
  • NULL values are excluded by default. You can include them by explicitly using INCLUDE NULLS.
  • Aggregation isn't allowed in the UNPIVOT block.
  • ROWSOURCE can't contain joins or unions but it can contain filters. Other transformations (except aggregations) are allowed after the UNPIVOT section and only by using the columns from the UNPIVOT section.
  • No GROUP BY is allowed in the dataset.
  • Primary key is mandatory and must include the columns from the WITHIN statement (it can exclude functionally dependent columns) and the key column from the UNPIVOT assignment.

Examples

Show product and sales amount as (name, value) pairs using id and fiscal year. 
DEFINE VERSIONED DATASET DW_SALES_UNPIVOT
    ROWSOURCE SALES;

    UNPIVOT INCLUDE NULLS // Nulls can be included/excluded. Exclude, by default
    (
        WITHIN SALES[ID, FISCAL_YEAR];
 
        // Target Columns SALES_AMT and PRODUCT are specified on LHS
        // The corresponding display values for PRODUCT column are specified in the LHS as well
        // Pairs on LHS map to columns on RHS, in sequence
        THIS[(PRODUCT: 'A', SALES_AMT), (PRODUCT : 'B', SALES_AMT)] = SALES[PROD_A_AMT, PROD_B_AMT];
           
    );

    PRIMARYKEY [ID, FISCAL_YEAR, PRODUCT];
END
Show product, sales amount, sales quantity using ID and fiscal year.
DEFINE VERSIONED DATASET DW_SALES_UNPIVOT
    ROWSOURCE SALES;
    
    UNPIVOT INCLUDE NULLS
    (
        WITHIN SALES[ID, FISCAL_YEAR];
         THIS[(PRODUCT : 'A', SALES_AMT), (PRODUCT :'B', SALES_AMT), (PRODUCT : 'A', SALES_QTY), (PRODUCT : 'B', SALES_QTY)]
               = SALES[PROD_A_AMT, PROD_B_AMT, PROD_A_QTY, PROD_B_QTY];
    );
 
    THIS[SALES_RATIO] = THIS.SALES_AMT/THIS.SALES_QTY;
 
    PRIMARYKEY [ID, FISCAL_YEAR,PRODUCT]; 
END
Show year, month and population using country and city.
DEFINE VERSIONED DATASET DW_SALES_UNPIVOT
    ROWSOURCE SALES;
    UNPIVOT
    (
        WITHIN SALES[COUNTRY, CITY, CITY_CODE];

        THIS[(YEAR : 2000, MONTH : 'Jan', POPULATION), (YEAR : 2000, MONTH : 'Feb', POPULATION), (YEAR : 2000, MONTH : 'Mar', POPULATION)] = SALES[SUM_POP_2000_JAN, SUM_POP_2000_FEB, SUM_POP_2000_MAR];
    );

    PRIMARYKEY [COUNTRY, CITY, YEAR, MONTH];
END

Block Load Definitions

Use block load for different load logic for full and incremental.

Pivot Load


pivot_code_block_load ::= 'ON FULL LOAD'
                              transposition_rowsource_specification
                              pivot_section
                              [ column_mapping_assignment ]...
                              [
                                  'INCREMENTAL LOAD'
                                  transposition_rowsource_specification
                                  pivot_section
                                  [ column_mapping_assignment ]...
                              ]
                              'ENDLOAD'
                              [ primary_key_specification ]
Example of Pivot Code block load:
DEFINE VERSIONED DATASET DW_CITY_PIVOT
    ON FULL LOAD
        ROWSOURCE CITIES;
        PIVOT
        (
            WITHIN CITIES[COUNTRY, CITY, CITY_CODE];
            THIS[AV_INC_JAN, AV_INC_FEB, AV_INC_MAR] = AVG(CITIES.INCOME) FOR CITIES.MONTH IN ('Jan', 'Feb', 'Mar');
        );
 
    INCREMENTAL LOAD
        ROWSOURCE CITIES_COUNTIES;
        PIVOT
        (
            WITHIN CITIES_COUNTIES[COUNTRY, CITY, CITY_CODE];
            THIS[AV_INC_JAN, AV_INC_FEB, AV_INC_MAR] = AVG(CITIES_COUNTIES.INCOME) FOR CITIES_COUNTIES.MONTH IN ('Jan', 'Feb', 'Mar');
        );
    ENDLOAD

    PRIMARYKEY [COUNTRY, CITY];
END

Unpivot Load


unpivot_code_block_load ::= 'ON FULL LOAD'
                                transposition_rowsource_specification
                                unpivot_section
                               	[ column_mapping_assignment ]...
                              	[
                                   'INCREMENTAL LOAD'
                                   transposition_rowsource_specification
                                   unpivot_section
                                   [ column_mapping_assignment ]...
                                                                ]
                             'ENDLOAD'
                             primary_key_specification