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
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
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
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 |
--------+-------+------+------+------+------+------+