How to Pivot Data in Snowflake

How to Pivot a Table in Snowflake

The PIVOT function in Snowflake allows you to transform rows into columns, making it easier to analyze categorical data. It is commonly used for reporting and data summarization.

Basic Syntax for PIVOT in Snowflake

SELECT *
FROM (
    SELECT category, region, sales
    FROM sales_data
) 
PIVOT (
    SUM(sales) FOR region IN ('North', 'South', 'East', 'West')
);

This transforms region values into column headers, displaying total sales per category across different regions.

Snowflake PIVOT with WHERE Clause

To filter results in a pivot table, apply a WHERE clause inside the subquery:

SELECT *
FROM (
    SELECT category, region, sales
    FROM sales_data
    WHERE year = 2024
) 
PIVOT (
    SUM(sales) FOR region IN ('North', 'South', 'East', 'West')
);

This limits the pivot transformation to data from 2024 only.

Snowflake PIVOT with Multiple Columns

You can pivot multiple columns by aggregating different measures simultaneously:

SELECT *
FROM (
    SELECT category, region, sales, profit
    FROM sales_data
) 
PIVOT (
    SUM(sales) AS total_sales, SUM(profit) AS total_profit
    FOR region IN ('North', 'South', 'East', 'West')
);

Here, both sales and profit are pivoted, creating separate columns for each measure.

Snowflake PIVOT Rows to Columns

Pivoting rows into columns is the primary function of PIVOT. You define the column to pivot on and the aggregated values:

SELECT *
FROM (
    SELECT product, month, revenue
    FROM sales_data
) 
PIVOT (
    SUM(revenue) FOR month IN ('Jan', 'Feb', 'Mar', 'Apr')
);

This results in each month becoming a separate column, showing total revenue per product.

Using Pivoted Snowflake Data in Evidence

Evidence supports Snowflake as a data source, so you can use pivoted queries as data sources in Evidence reports.

However, Evidence also comes with a browser-based query engine, which allows you to write SQL queries in Markdown, which can be used to transform data sources. This is useful as it allows user inputs to cause data interactivity that is described in SQL.

This is done using DuckDB’s simplified PIVOT syntax, which is more intuitive than Snowflake’s approach.

Example: Pivoting Data in Evidence using DuckDB

PIVOT sales_data
ON region
USING SUM(sales)
GROUP BY category;

This syntax automatically detects unique values in the region column and turns them into separate columns without needing to manually list them.

For more complex use cases, you can combine multiple aggregations, filter pivoted columns, or group data at different levels:

PIVOT sales_data
ON region, category
USING SUM(sales) AS total_sales, MAX(sales) AS max_sales;

This ensures fully controlled transformations using SQL before rendering reports, making Evidence a more streamlined approach for pivoting data.

PIVOTs in Snowflake and Evidence

PIVOT in Snowflake is a powerful tool for transforming rows into columns.
WHERE clauses can be used within the subquery to filter data before pivoting.
Multiple columns can be pivoted simultaneously for richer insights.
In Evidence, DuckDB’s simplified PIVOT syntax makes it easier to pivot data dynamically without explicitly listing all values.