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 |
