How can you UNPIVOT in Snowflake ?

UNPIVOT in Snowflake

UNPIVOT will rotate a table by transforming columns into rows. UNPIVOT is a relational operator which accepts two columns (from a table or subquery), along with a list of columns, and generates a row for each column specified in the list. In a Snowflake query, it is specified in the FROM clause after the table name or subquery.

Important thing is that UNPIVOT is not exactly the reverse of PIVOT . UNPIVOT cannot undo aggregations made by PIVOT.

UNPIVOT operator can be used to transform a wide table into a narrower table

Create table :

create or replace table device_sales(items int, dept text,jan int, feb int, mar int, april int,may int);

Insert into the created table

insert into device_sales values
(1, 'electronics', 120, 100, 400, 100,50),
(2, 'clothes', 230, 90, 150, 100,220),
(3, 'cars', 400, 200, 150, 50,360);

— UNPIVOT example

select * from device_sales unpivot(sales for month in (jan, feb, mar, april,may )) order by items;

ITEMS DEPT MONTH SALES
1 electronics JAN 120
1 electronics FEB 100
1 electronics MAR 400
1 electronics APRIL 100
1 electronics MAY 50
2 grocessery JAN 230
2 grocessery FEB 90
2 grocessery MAR 150
2 grocessery APRIL 100
2 grocessery MAY 220
3 furniture JAN 400
3 furniture FEB 200
3 furniture MAR 150
3 furniture APRIL 50
3 furniture MAY 360

Pivot in Snowflake

Author: user

Leave a Reply