TD_UNPIVOT

Translation specification for the transfomation of TD_UNPIVOT into a equivalent query in Snowflake

Description

TD_UNPIVOT in Teradata can unpivot multiple columns at once, while Snowflake UNPIVOT can only unpivot a single column. The unpivot functionality is used to transform columns of the specified table into rows. For more information see TD_UNPIVOT.

[TD_SYSFNLIB.] TD_UNPIVOT (
  ON { tableName | ( query_expression ) }
  USING VALUE_COLUMNS ( 'value_columns_value' [,...] )
  UNPIVOT_COLUMN ( 'unpivot_column_value' )
  COLUMN_LIST ( 'column_list_value' [,...] )
  [ COLUMN_ALIAS_LIST ( 'column_alias_list_value' [,...] )
      INCLUDE_NULLS ( { 'No' | 'Yes' } )
  ]
)

The following transformation is able to generate a SQL query in Snowflake that unpivots multiple columns at the same time, the same way it works in Teradata.

Sample Source Patterns

Setup data title

Teradata

Query

CREATE TABLE superunpivottest (
	myKey INTEGER NOT NULL PRIMARY KEY,
	firstSemesterIncome DECIMAL(10,2),
	secondSemesterIncome DECIMAL(10,2),
	firstSemesterExpenses DECIMAL(10,2),
	secondSemesterExpenses DECIMAL(10,2)
);

INSERT INTO superUnpivottest VALUES (2020, 15440, 25430.57, 10322.15, 12355.36);
INSERT INTO superUnpivottest VALUES (2018, 18325.25, 25220.65, 15560.45, 15680.33);
INSERT INTO superUnpivottest VALUES (2019, 23855.75, 34220.22, 14582.55, 24122);

Snowflake

Query

CREATE TABLE superunpivottest (
myKey INTEGER NOT NULL PRIMARY KEY,
firstSemesterIncome DECIMAL(10,2),
secondSemesterIncome DECIMAL(10,2),
firstSemesterExpenses DECIMAL(10,2),
secondSemesterExpenses DECIMAL(10,2)
);

INSERT INTO superUnpivottest VALUES (2020, 15440, 25430.57, 10322.15, 12355.36);

INSERT INTO superUnpivottest VALUES (2018, 18325.25, 25220.65, 15560.45, 15680.33);

INSERT INTO superUnpivottest VALUES (2019, 23855.75, 34220.22, 14582.55, 24122);

TD_UNPIVOT transformation

Teradata

Query

SELECT * FROM
 TD_UNPIVOT(
 	ON superunpivottest
 	USING
 	VALUE_COLUMNS('Income', 'Expenses')
 	UNPIVOT_COLUMN('Semester')
 	COLUMN_LIST('firstSemesterIncome, firstSemesterExpenses', 'secondSemesterIncome, secondSemesterExpenses')
    COLUMN_ALIAS_LIST('First', 'Second')
 )X ORDER BY mykey, Semester;

Result

myKey |Semester |Income   | Expenses |
------+---------+---------+----------+
2018  |First    |18325.25 |15560.45  |
------+---------+---------+----------+
2018  |Second   |25220.65 |15680.33  |
------+---------+---------+----------+
2019  |First    |23855.75 |14582.55  |
------+---------+---------+----------+
2019  |Second   |34220.22 |24122.00  |
------+---------+---------+----------+
2020  |First    |15440.00 |10322.15  |
------+---------+---------+----------+
2020  |Second   |25430.57 |12355.36  |

Snowflake

Query

SELECT
*
FROM (SELECT myKey,
TRIM(GET_IGNORE_CASE(OBJECT_CONSTRUCT('FIRSTSEMESTERINCOME', 'First', 'FIRSTSEMESTEREXPENSES', 'First', 'SECONDSEMESTERINCOME', 'Second', 'SECONDSEMESTEREXPENSES', 'Second'), Semester), '"') AS Semester,
Income,
Expenses FROM superunpivottest UNPIVOT(Income FOR Semester IN (firstSemesterIncome, secondSemesterIncome)) UNPIVOT(Expenses FOR Semester1 IN (firstSemesterExpenses, secondSemesterExpenses)) WHERE Semester = 'FIRSTSEMESTERINCOME' AND Semester1 = 'FIRSTSEMESTEREXPENSES' OR Semester = 'SECONDSEMESTERINCOME' AND Semester1 = 'SECONDSEMESTEREXPENSES') ORDER BY mykey, Semester;

Result

myKey |Semester |Income   | Expenses |
------+---------+---------+----------+
2018  |First    |18325.25 |15560.45  |
------+---------+---------+----------+
2018  |Second   |25220.65 |15680.33  |
------+---------+---------+----------+
2019  |First    |23855.75 |14582.55  |
------+---------+---------+----------+
2019  |Second   |34220.22 |24122.00  |
------+---------+---------+----------+
2020  |First    |15440.00 |10322.15  |
------+---------+---------+----------+
2020  |Second   |25430.57 |12355.36  |

Known Issues

1.TD_UNPIVOT with INCLUDE_NULLS clause set to YES is not supported

Snowflake UNPIVOT function used in the transformation will ignore null values always, and the user will be warned that the INCLUDE_NULLS clause is not supported when it is set to YES.

2. Table information is required to correctly transform the function

SnowConvert needs the name of the columns that are being used in the TD_UNPIVOT function, if the user does not include the columns list in the query_expression of the function but provides the name of the table being unpivoted then it will try to retrieve the column names from the table definition. If the names can not be found then the user will be warned that the resulting query might be losing columns in the result.

Related EWIs

1. MSCEWI1021: Node Not supported.

2. MSCEWI2061: TD_UNPIVOT transformation requires column information that could not be found, columns missing in result.