Bird
Raised Fist0
SnowflakeHow-ToBeginner · 4 min read

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 IN list, 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

PartDescriptionExample
AggregationFunction to combine valuesSUM(amount)
Pivot ColumnColumn whose values become columnsregion
Values ListValues to turn into columns'North', 'South', 'East', 'West'
UsageUsed inside SELECT with PIVOT clauseSELECT * 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.