Snowflake PIVOT: Turn Rows Into Columns
Imagine you have a long list of sales data by month and you want to turn that list into an easy-to-read table where each month's sales are in their own column. The PIVOT function in Snowflake allows you to do just that, transforming data from a long "tall" format to a wide format where each unique value in a column becomes a separate column in the output.
What it does
PIVOT takes values from one column in your data and spreads them across multiple columns, so you can see each value as its own feature. This is particularly useful for reports or visualizations where you want to compare categories side by side.
Syntax
Here’s how you set up a PIVOT:
_10SELECT ..._10FROM ..._10 PIVOT ( <aggregate_function> ( <pivot_column> )_10 FOR <value_column> IN ( <pivot_value_1> [ , <pivot_value_2> ... ] ) )
where:
<aggregate_function>: This is the function used to summarize the data, such asSUM,COUNT,MAX, etc.<pivot_column>: The column whose values you want to summarize.<value_column>: The column that has the values that will become new column headers.<pivot_value_N>: The specific values in the value column that you want to turn into individual columns.
Examples
Transposing monthly data with PIVOT
Suppose you're managing sales data and you have a table where each row represents sales data for a month for each employee. You want to see each month's sales as its own column rather than as a list.
Input
Table: sales_data
| employee_id | month | sales |
|---|---|---|
| 1 | January | 500 |
| 1 | February | 450 |
| 2 | January | 700 |
| 2 | February | 650 |
Snowflake SQL Query
_10SELECT_10 employee_id,_10 jan,_10 feb_10FROM_10 sales_data PIVOT (SUM(sales) FOR month IN ('January', 'February')) AS pvt (employee_id, jan, feb);
Output
| employee_id | jan | feb |
|---|---|---|
| 1 | 500 | 450 |
| 2 | 700 | 650 |
Explanation
- The
PIVOTfunction in this query creates two new columns: one for January and one for February. - It sums up the sales for each month and displays them under the respective new columns.
- This makes it much easier to compare sales performance by month for each employee directly.
