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!

--

--