PIVOT

Translation specification for the PIVOT function form Teradata to Snowflake

Some parts in the output code are omitted for clarity reasons.

Description

The pivot function is used to transform rows of a table into columns. For more information check the PIVOT Teradata documentation.

PIVOT ( pivot_spec )
  [ WITH with_spec [,...] ]
  [AS] derived_table_name [ ( cname [,...] ) ]
  
pivot_spec := aggr_fn_spec [,...] FOR for_spec

aggr_fn_spec := aggr_fn ( cname ) [ [AS] pvt_aggr_alias ]

for_spec := { cname IN ( expr_spec_1 [,...] ) |
( cname [,...] ) IN ( expr_spec_2 [,...] ) |
cname IN ( subquery )
}

expr_spec_1 := expr [ [AS] expr_alias_name ]

expr_spec_2 := ( expr [,...] ) [ [AS] expr_alias_name ]

with_spec := aggr_fn ( { cname [,...] | * } ) [AS] aggr_alias

Sample Source Patterns

Setup data

Teradata

Query

IN -> Teradata_01.sql

CREATE TABLE star1(
	country VARCHAR(20),
	state VARCHAR(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

Query

OUT -> Teradata_01.sql

CREATE OR REPLACE TABLE star1 (
	country VARCHAR(20),
	state VARCHAR(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 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);

Basic PIVOT transformation

Teradata

Query

IN -> Teradata_02.sql

SELECT *
FROM star1 PIVOT (
	SUM(sales) FOR qtr                                                                                               
    IN ('Q1',                                                                                                     
    	'Q2', 
        'Q3')
)Tmp;

Result

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

Query

OUT -> Teradata_02.sql

SELECT
	*
FROM
	star1 PIVOT(
	SUM(sales) FOR qtr IN ('Q1',
	   	'Q2',
	       'Q3'))Tmp;

Result

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

Query

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;

Result

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

Query

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;

Result

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

1. SSC-EWI-0015: The input pivot/unpivot statement form is not supported