How to do Pivot in Snowflake ?

PIVOT in Snowflake

PIVOT rotates a table by turning the unique values from one column in the input expression into multiple columns and aggregating results where required on any remaining column values. PIVOT is specified in the FROM clause after the table name or subquery.

PIVOT supports the built-in aggregate functions AVG, COUNT, MAX, MIN, and SUM.

PIVOT can be used to transform a narrow table into a wider table.

–Table Create

create or replace table sales_stat(deviceid int, amount int, month text);

–Table insert data

insert into sales_stat values
(1, 100, 'JAN'),
(1, 200, 'JAN'),
(2, 250, 'JAN'),
(2, 250, 'JAN'),
(1, 100, 'FEB'),
(1, 200, 'FEB'),
(2, 200, 'FEB'),
(2, 905, 'FEB'),
(1, 400, 'MAR'),
(1, 600, 'MAR'),
(2, 500, 'MAR'),
(2, 900, 'MAR');

–Table Select for checking data is inserted 

select * from sales_stat;

Rotates a table by using Pivot 

select * 
from sales_stat
pivot(sum(amount) for month in ('JAN', 'FEB', 'MAR'))

Result

+----------+-------+-------+-------+
| DEVICEID | 'JAN' | 'FEB' | 'MAR' |
|----------+-------+-------+-------+
| 1        | 300   | 300   | 1000  |
| 2        | 500   | 1105  | 1400  |
+----------+-------+-------+-------+

To get column names without quotes

select * 
from sales_stat
pivot(sum(amount) for month in ('JAN', 'FEB', 'MAR'))
as q_remove (emp_id_renamed, jan, feb, mar)

Result

+----------+-------+-------+-------+
| DEVICEID | JAN | FEB  | MAR      |
|----------+-------+-------+-------+
| 1        | 300 | 300  | 1000     |
| 2        | 500 | 1105 | 1400     |
+----------+-------+-------+-------+

UNPIVOT in Snowflake