Snowflake Supports Dynamic Pivot

Photo by James Harrison on Unsplash

Snowflake just made the PIVOT functionality more flexible and intuitive. You can now dynamically specify the list of values to pivot by using the ANY keyword or a subquery.

Understanding Dynamic Pivot

Let’s walk through an example to illustrate the power of dynamic pivot. Imagine you have a dataset containing quarterly sales data, segmented by region. Traditionally, if you wanted to pivot this data to analyze quarterly sales trends for the year 2023, you would need to explicitly specify each quarter in the PIVOT syntax.

CREATE OR REPLACE TABLE sales_data AS
 SELECT *
 FROM (
  VALUES
    (2023, 'Q1', 'North_America', 60000),
    (2023, 'Q1', 'Europe', 45000),
    (2023, 'Q1', 'Asia_Pacific', 55000),
    (2023, 'Q1', 'Latin_America', 35000),
    (2023, 'Q2', 'North_America', 65000),
    (2023, 'Q2', 'Europe', 48000),
    (2023, 'Q2', 'Asia_Pacific', 60000),
    (2023, 'Q2', 'Latin_America', 40000),
    (2023, 'Q3', 'North_America', 70000),
    (2023, 'Q3', 'Europe', 50000),
    (2023, 'Q3', 'Asia_Pacific', 65000),
    (2023, 'Q3', 'Latin_America', 42000),
    (2023, 'Q4', 'North_America', 75000),
    (2023, 'Q4', 'Europe', 53000),
    (2023, 'Q4', 'Asia_Pacific', 70000),
    (2023, 'Q4', 'Latin_America', 45000)
 ) AS t(fis_year, fis_quarter, region, total_sales);

SELECT fis_year, fis_quarter, region, total_sales 
FROM sales_data 
WHERE fis_year = 2023;

 fis_year   fis_quarter      region       total_sales  
 ---------- ------------- --------------- ------------- 
      2023   Q1            North_America         60000  
      2023   Q1            Europe                45000  
      2023   Q1            Asia_Pacific          55000  
      2023   Q1            Latin_America         35000  
      2023   Q2            North_America         65000  
      2023   Q2            Europe                48000  
      2023   Q2            Asia_Pacific          60000  
      2023   Q2            Latin_America         40000  
      2023   Q3            North_America         70000  
      2023   Q3            Europe                50000  
      2023   Q3            Asia_Pacific          65000  
      2023   Q3            Latin_America         42000  
      2023   Q4            North_America         75000  
      2023   Q4            Europe                53000  
      2023   Q4            Asia_Pacific          70000  
      2023   Q4            Latin_America         45000 

Static Pivot

In the current PIVOT syntax, you might write a query like this:

SELECT *
FROM sales_data
PIVOT (
SUM(total_sales)
FOR fis_quarter IN ('Q1', 'Q2', 'Q3', 'Q4')
) AS PivotTable
WHERE fis_year IN (2023)
ORDER BY region;

+----------+---------------+-------+-------+-------+-------+
| fis_year |    region     |  Q1   |  Q2   |  Q3   |  Q4   |
+----------+---------------+-------+-------+-------+-------+
|     2023 | Asia_Pacific  | 55000 | 60000 | 65000 | 70000 |
|     2023 | Europe        | 45000 | 48000 | 50000 | 53000 |
|     2023 | Latin_America | 35000 | 40000 | 42000 | 45000 |
|     2023 | North_America | 60000 | 65000 | 70000 | 75000 |
+----------+---------------+-------+-------+-------+-------+

In cases where you don’t know the values for fis_quarter, you might dynamically construct your PIVOT query using a query like this:

SELECT 'SELECT * FROM sales_data 
PIVOT (SUM(total_sales) FOR fis_quarter 
IN ('||LISTAGG(DISTINCT ''''||fis_quarter||'''', ',') ||')) 
WHERE fis_year IN (2023) ORDER BY region;' 
FROM sales_data;

-- SELECT * FROM sales_data PIVOT (SUM(total_sales) 
-- FOR fis_quarter IN ('Q1','Q2','Q3','Q4')) 
-- WHERE fis_year IN (2023) ORDER BY region;

This approach is only semi-dynamic, requires multiple steps and is still difficult to use.

Embracing Dynamic Pivot

With dynamic pivot, you can achieve the same result with much greater flexibility. By simply using the ANY keyword, you can pivot on all distinct values of the pivot column automatically, as shown in the following query:

SELECT *
FROM sales_data
PIVOT (
SUM(total_sales)
FOR fis_quarter IN (ANY)
) AS PivotTable
WHERE fis_year IN (2023)
ORDER BY region;

Additionally, you can specify the list of values using a subquery, allowing for even more dynamic control over the pivoted data. For example, you can pivot only on quarters where quarterly sales exceed a certain threshold, as demonstrated in the following query:

SELECT *
FROM sales_data
PIVOT (
SUM(total_sales)
FOR fis_quarter IN (SELECT DISTINCT fis_quarter FROM sales_data GROUP BY fis_quarter HAVING sum(total_sales) > 200000 ORDER BY fis_quarter DESC)
) AS PivotTable
ORDER BY region;

+----------+---------------+-------+-------+-------+
| fis_year |    region     |  Q4   |  Q3   |  Q2   |
+----------+---------------+-------+-------+-------+
|     2023 | Asia_Pacific  | 70000 | 65000 | 60000 |
|     2023 | Europe        | 53000 | 50000 | 48000 |
|     2023 | Latin_America | 45000 | 42000 | 40000 |
|     2023 | North_America | 75000 | 70000 | 65000 |
+----------+---------------+-------+-------+-------+

Note that with dynamic pivot, you can also use the ORDER BY clause to specify the ordering of the pivoting columns.

We hope that the dynamic pivot enhancement will make your data analysis workflows more efficient and productive. For more information, please refer to our documentation.

Happy PIVOTing!