CREATETABLEstar1( country VARCHAR(20),stateVARCHAR(10), yr INTEGER, qtr VARCHAR(3), sales INTEGER, cogs INTEGER);insert into star1 values ('USA', 'CA', 2001, 'Q1', 30, 15);insert into star1 values ('Canada', 'ON', 2001, 'Q2', 10, 0);insert into star1 values ('Canada', 'BC', 2001, 'Q3', 10, 0);insert into star1 values ('USA', 'NY', 2001, 'Q1', 45, 25);insert into star1 values ('USA', 'CA', 2001, 'Q2', 50, 20);
Snowflake
OUT -> Teradata_01.sql
CREATE OR REPLACETABLEstar1 ( country VARCHAR(20),stateVARCHAR(10), yr INTEGER, qtr VARCHAR(3), sales INTEGER, cogs INTEGER)COMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}';INSERT INTO star1VALUES ('USA', 'CA', 2001, 'Q1', 30, 15);INSERT INTO star1VALUES ('Canada', 'ON', 2001, 'Q2', 10, 0);INSERT INTO star1VALUES ('Canada', 'BC', 2001, 'Q3', 10, 0);INSERT INTO star1VALUES ('USA', 'NY', 2001, 'Q1', 45, 25);INSERT INTO star1VALUES ('USA', 'CA', 2001, 'Q2', 50, 20);
Basic PIVOT transformation
Teradata
IN -> Teradata_02.sql
SELECT*FROM star1 PIVOT (SUM(sales) FOR qtr IN ('Q1', 'Q2', 'Q3'))Tmp;
Country | State | yr | cogs | 'Q1' | 'Q2' | 'Q3' |
--------+-------+------+------+------+------+------+
Canada | BC | 2001 | 0 | null | null | 10 |
--------+-------+------+------+------+------+------+
USA | NY | 2001 | 25 | 45 | null | null |
--------+-------+------+------+------+------+------+
Canada | ON | 2001 | 0 | null | 10 | null |
--------+-------+------+------+------+------+------+
USA | CA | 2001 | 20 | null | 50 | null |
--------+-------+------+------+------+------+------+
USA | CA | 2001 | 15 | 30 | null | null |
--------+-------+------+------+------+------+------+
Snowflake
OUT -> Teradata_02.sql
SELECT*FROM star1 PIVOT(SUM(sales) FOR qtr IN ('Q1','Q2','Q3'))Tmp;
Country | State | yr | cogs | 'Q1' | 'Q2' | 'Q3' |
--------+-------+------+------+------+------+------+
Canada | BC | 2001 | 0 | null | null | 10 |
--------+-------+------+------+------+------+------+
USA | NY | 2001 | 25 | 45 | null | null |
--------+-------+------+------+------+------+------+
Canada | ON | 2001 | 0 | null | 10 | null |
--------+-------+------+------+------+------+------+
USA | CA | 2001 | 20 | null | 50 | null |
--------+-------+------+------+------+------+------+
USA | CA | 2001 | 15 | 30 | null | null |
--------+-------+------+------+------+------+------+
PIVOT with aliases transformation
Teradata
IN -> Teradata_03.sql
SELECT*FROM star1 PIVOT (SUM(sales) as ss1 FOR qtr IN ('Q1'AS Quarter1, 'Q2'AS Quarter2, 'Q3'AS Quarter3))Tmp;
Country | State | yr | cogs | Quarter1_ss1 | Quarter2_ss1 | Quarter3_ss1 |
--------+-------+------+------+--------------+--------------+--------------+
Canada | BC | 2001 | 0 | null | null | 10 |
--------+-------+------+------+--------------+--------------+--------------+
USA | NY | 2001 | 25 | 45 | null | null |
--------+-------+------+------+--------------+--------------+--------------+
Canada | ON | 2001 | 0 | null | 10 | null |
--------+-------+------+------+--------------+--------------+--------------+
USA | CA | 2001 | 20 | null | 50 | null |
--------+-------+------+------+--------------+--------------+--------------+
USA | CA | 2001 | 15 | 30 | null | null |
--------+-------+------+------+--------------+--------------+--------------+
Snowflake
OUT -> Teradata_03.sql
SELECT*FROM !!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT RENAME COLUMN NOT SUPPORTED ***/!!! star1 PIVOT(SUM(sales) FOR qtr IN ( !!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT RENAME COLUMN NOT SUPPORTED ***/!!!'Q1', !!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT RENAME COLUMN NOT SUPPORTED ***/!!!'Q2', !!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT RENAME COLUMN NOT SUPPORTED ***/!!!'Q3'))Tmp;
Country | State | yr | cogs | Quarter1_ss1 | Quarter2_ss1 | Quarter3_ss1 |--------+-------+------+------+--------------+--------------+--------------+Canada | BC | 2001 | 0 | null | null | 10 |--------+-------+------+------+--------------+--------------+--------------+USA | NY | 2001 | 25 | 45 | null | null |--------+-------+------+------+--------------+--------------+--------------+Canada | ON | 2001 | 0 | null | 10 | null |--------+-------+------+------+--------------+--------------+--------------+USA | CA | 2001 | 20 | null | 50 | null |--------+-------+------+------+--------------+--------------+--------------+USA | CA | 2001 | 15 | 30 | null | null |--------+-------+------+------+--------------+--------------+--------------+
Known Issues
1. WITH clause not supported
Using the WITH clause is not currently supported.
2. Pivot over multiple pivot columns not supported
Snowconvert is transforming the PIVOT function into the PIVOT function in Snowflake, which only supports applying the function over a single column.
3. Pivot with multiple aggregate functions not supported
The PIVOT function in Snowflake only supports applying one aggregate function over the data.
4. Subquery in the IN clause not supported
The IN clause of the Snowflake PIVOT function does not accept subqueries.
5. Aliases only supported if all IN clause elements have it and table specification is present
For the column names with aliases to be equivalent, SnowConvert requires that all the values specified in the IN clause have one alias specified and the table specification is present in the input code, this is necessary so SnowConvert can successfully create the alias list for the resulting table.
Related EWIs
SSC-EWI-0015: The input pivot/unpivot statement form is not supported