How to Use PIVOT in Snowflake: Syntax and Examples
In Snowflake, use the
PIVOT clause to rotate rows into columns by specifying an aggregation function and the values to pivot on. The syntax includes PIVOT(aggregation(column) FOR pivot_column IN (...)) inside a SELECT statement to reshape your data easily.Syntax
The PIVOT clause in Snowflake transforms rows into columns by aggregating data. It requires three parts:
- Aggregation function: e.g.,
SUM(sales)to combine values. - Pivot column: the column whose values become new columns.
- Values list: the specific values from the pivot column to turn into columns.
sql
SELECT * FROM sales_data PIVOT( SUM(amount) FOR region IN ('North', 'South', 'East', 'West') );
Example
This example shows how to pivot sales amounts by region, turning region values into columns with their total sales.
sql
WITH sales_data AS ( SELECT '2024-01' AS month, 'North' AS region, 100 AS amount UNION ALL SELECT '2024-01', 'South', 150 UNION ALL SELECT '2024-01', 'East', 200 UNION ALL SELECT '2024-02', 'North', 120 UNION ALL SELECT '2024-02', 'South', 180 UNION ALL SELECT '2024-02', 'West', 90 ) SELECT * FROM sales_data PIVOT( SUM(amount) FOR region IN ('North', 'South', 'East', 'West') ) ORDER BY month;
Output
MONTH | NORTH | SOUTH | EAST | WEST
-------|-------|-------|------|-----
2024-01| 100 | 150 | 200 | NULL
2024-02| 120 | 180 | NULL | 90
Common Pitfalls
Common mistakes when using PIVOT in Snowflake include:
- Not specifying all pivot values in the
INlist, which leads to missing columns. - Using non-aggregated columns outside the pivot without grouping, causing errors.
- Confusing the pivot column and the aggregation column.
Always ensure the aggregation function matches your data needs and the pivot values cover all expected categories.
sql
/* Wrong: Missing pivot values and no aggregation */ SELECT * FROM sales_data PIVOT( amount FOR region IN ('North', 'South') ); /* Right: Use aggregation and full pivot values */ SELECT * FROM sales_data PIVOT( SUM(amount) FOR region IN ('North', 'South', 'East', 'West') );
Quick Reference
| Part | Description | Example |
|---|---|---|
| Aggregation | Function to combine values | SUM(amount) |
| Pivot Column | Column whose values become columns | region |
| Values List | Values to turn into columns | 'North', 'South', 'East', 'West' |
| Usage | Used inside SELECT with PIVOT clause | SELECT * FROM table PIVOT(...) |
Key Takeaways
Use PIVOT with an aggregation function to convert rows into columns in Snowflake.
Specify the pivot column and list all values you want as columns in the IN clause.
Always aggregate data to avoid errors and missing results.
Check that your pivot values cover all categories you want to display.
PIVOT helps summarize and reshape data for easier analysis.
