Snowflake Pivot 101: How to master Pivot and Unpivot (2024)

Pivoting and unpivoting are essential techniques in Snowflake—they stand as crucial feature in data transformation. These techniques help you to transpose your data, converting rows into columns and vice versa. Such transformations are not just cosmetic; they pave the way for smarter data analysis and reporting.

In this article, we will explore what pivoting and unpivoting operations do, their use cases, and how to leverage built-in Snowflake PIVOT and Snowflake UNPIVOT functions to put these concepts into practice.

Understanding Basics of Snowflake PIVOT and UNPIVOT

Definition of Pivoting:

Pivoting is the process of rotating data from row format to column format. Imagine having a table where each row represents a month, and you want to turn those months into individual columns. That's where pivoting comes into play.

Visually, this transformation can be represented as:

Diagram showing transformation from a vertical to a pivoted table format - Snowflake pivot - Snowflake unpivot
Diagram showing transformation from a vertical to a pivoted table format

Definition of Unpivoting:

Unpivoting does the exact reverse operation—transforming columns into rows format. If you have a wide table with many columns that you'd rather represent as rows, unpivoting is your go-to operation.

Visually, this transformation can be represented as:

Diagram showing the inpivoting process - Snowflake pivot - Snowflake unpivot
Diagram showing the unpivoting process

In general,

Diagram showing Snowflake PIVOT and UNPIVOT operations - Snowflake unpivot
Diagram showing Snowflake PIVOT and UNPIVOT operations

Common Use Case of Snowflake PIVOT

  • Data Summarization: Pivoting can help summarize data, turning detailed rows into summarized columns.
  • Cross-Tabulation: Creating matrix formats for data representation.
  • Data Reporting: Making data more readable and presentable for reports.

Common Use Case of Snowflake UNPIVOT

  • Data Normalization: Turning wide tables with redundant columns into a more normalized format.
  • Data Preparation: Preparing data for tools or applications that require a specific row-based format.
  • Data Cleaning: Simplifying complex datasets by reducing the number of columns.
Note: UNPIVOT is NOT exactly the reverse of PIVOT as it cannot undo aggregations made by PIVOT.

Now let's look at how Snowflake PIVOT and UNPIVOT commands can be used to accomplish pivoting and unpivoting data.

How to Make Use of Snowflake PIVOT Command?

Snowflake's PIVOT is a SQL operation used to transform rows into columns. It's particularly useful when you want to convert unique row values from one column into multiple columns in the output, aggregating data in the process. This operation is common in data analysis and reporting tasks.

Using PIVOT, the unique values from a specific column that were previously organized into separate rows can be rotated to align related data points into columns instead. This pivoting of the data structure condenses the information, providing a more consolidated analytical view. Here's a basic syntax for using Snowflake PIVOT command:

Syntax for Snowflake PIVOT

Snowflake PIVOT must be used within the From clause exclusively. When dealing with a subquery, it should be written within the subquery's From clause.

The syntax is:

SELECT ...
FROM ...
   PIVOT (
      <aggregate_function>(<pivot_column>)
      FOR <value_column> 
      IN (<pivot_values>)
   )

The key parameters are:

  • aggregate_function: Aggregation you want to apply, like AVG, COUNT, MAX, MIN, and SUM.
  • pivot_column: Column you want to turn into new columns.
  • value_column: Values you want to populate in the new columns.
  • pivot_values: List of values in the pivot_column that you want to turn into new columns.

For example, consider the following example data table containing the school's student grades for different subjects:

First, let's create on table called StudentGrades and insert some values into that table.

CREATE TABLE StudentGrades (
    StudentID INT,
    Subject VARCHAR,
    Grade INT
);

INSERT INTO StudentGrades (StudentID, Subject, Grade)
VALUES 
(3, 'Math', 92),
(3, 'History', 89),
(3, 'Science', 94),
(3, 'English', 88),
(4, 'Math', 76),
(4, 'History', 84),
(4, 'Science', 82),
(4, 'English', 91),
(5, 'Math', 89),
(5, 'History', 87),
(5, 'Science', 90),
(5, 'English', 93),
(6, 'Math', 79),
(6, 'History', 85),
(6, 'Science', 88),
(6, 'English', 86),
(7, 'Math', 83),
(7, 'History', 88),
(7, 'Science', 85),
(7, 'English', 87),
(8, 'Math', 90),
(8, 'History', 86),
(8, 'Science', 89),
(8, 'English', 92),
(9, 'Math', 88),
(9, 'History', 87),
(9, 'Science', 86),
(9, 'English', 90),
(10, 'Math', 91),
(10, 'History', 85),
(10, 'Science', 87),
(10, 'English', 88);
Creating and inserting values into the 'StudentGrades' table in Snowflake - Snowflake pivot - Snowflake unpivot
Creating and inserting values into the 'StudentGrades' table in Snowflake

Now, lets pivot the table so that each subject becomes a separate column.

SELECT * FROM StudentGrades
PIVOT (MAX(Grade) FOR Subject IN ('Math', 'History')) AS p;

This would output:

Pivoting table so that each subject becomes a separate column StudentGrades table - Snowflake pivot - Snowflake unpivot
Pivoting table so that each subject becomes a separate column StudentGrades table

The pivot has aggregated each of the student's grades for Math and History as separate columns.

Getting column name without quotes(“ “) using Snowflake PIVOT

Remember that you can also include the column names in the AS clause if you prefer them without quotes, or if you wish to display different column names, like this:

SELECT * FROM StudentGrades
PIVOT (MAX(Grade) FOR Subject IN ('Math', 'History')) AS p (student_id, math, history);
Getting column name without quotes using Snowflake PIVOT - Snowflake pivot - Snowflake unpivot
Getting column name without quotes using Snowflake PIVOT

How to pivot multiple columns?

Snowflake PIVOT function is really powerful, it's designed to pivot on a single aggregate function. But what if you want to pivot on multiple aggregates, like both SUM and AVERAGE? There's no direct way, but with the clever use of UNION, you can achieve this.

Let's say you have a table BookSales that tracks the number of books sold and the total revenue for different genres.

Step-by-Step Guide to Pivot Multiple Columns in Snowflake

1) Creating the Table

CREATE TABLE BookSales (
    BookID INT,
    Genre VARCHAR,
    BooksSold INT,
    Revenue FLOAT
);

2) Inserting Data

INSERT INTO BookSales (BookID, Genre, BooksSold, Revenue)
VALUES 
(1, 'Fiction', 100, 1500.00),
(2, 'Non-Fiction', 80, 1200.00),
(3, 'Fiction', 110, 1650.00),
(4, 'Non-Fiction', 85, 1275.00),
(5, 'Fiction', 105, 1575.00),
(6, 'Non-Fiction', 90, 1350.00),
(7, 'Fiction', 95, 1425.00),
(8, 'Non-Fiction', 88, 1320.00),
(9, 'Fiction', 108, 1620.00),
(10, 'Non-Fiction', 86, 1290.00);

3) Pivoting Multiple Columns

To pivot on both the SUM and AVERAGE of BooksSold and Revenue for each genre, you can use the following query:

-- Total Revenue
SELECT 'Total Revenue' AS Metric, *
FROM (
    SELECT Genre, Revenue
    FROM BookSales
) AS SourceTable
PIVOT (
    SUM(Revenue) 
    FOR Genre IN ('Fiction', 'Non-Fiction')
) AS PivotTable

UNION ALL

-- Average Revenue
SELECT 'Average Revenue' AS Metric, *
FROM (
    SELECT Genre, Revenue
    FROM BookSales
) AS SourceTable
PIVOT (
    AVG(Revenue) 
    FOR Genre IN ('Fiction', 'Non-Fiction')
) AS PivotTable;
Pivoting on both the SUM and AVERAGE of BooksSold and Revenue for each genre - Snowflake pivot - Snowflake unpivot
Pivoting on both the SUM and AVERAGE of BooksSold and Revenue for each books genre

How to Make Use of Snowflake UNPIVOT Command?

Snowflake UNPIVOT function transforms columns into rows. It takes two columns plus a list of columns to unpivot and outputs a row for each specified column. UNPIVOT pivots table data from columns back into rows, reversing the effect of a pivot operation. However, it is important to note that Snowflake UNPIVOT is not exactly the reverse of PIVOT, as it cannot undo aggregations made by the Snowflake PIVOT function. Here's a basic syntax on how to use the Snowflake UNPIVOT command:

The syntax is:

SELECT ...
FROM ...
  UNPIVOT (
     <value_column>
     FOR <name_column>
     IN (<column_list>)
  )

The key parameters are:

  • value_column: Column that will hold the values from the unpivoted columns.
  • name_column: Column that will hold the names of the unpivoted columns.
  • column_list: List of columns you want to unpivot.

For example, consider the following hypothetical scenario where we track the quantity of fruits in two different stores.

1) Creating the Table and Inserting Dummy Values

Let's use a simple example of a FruitInventory table that tracks the quantity/number of fruits in two stores: "StoreA" and "StoreB".

CREATE TABLE FruitInventory (
    FruitName VARCHAR,
    StoreA INT,
    StoreB INT
);

Let's insert data for different fruits.

INSERT INTO FruitInventory (FruitName, StoreA, StoreB)
VALUES 
('Apple', 100, 120),
('Banana', 80, 90),
('Cherry', 50, 45),
('Date', 30, 35),
('Fig', 40, 38),
('Grape', 60, 65),
('Honeydew', 20, 18),
('Kiwi', 25, 28),
('Lemon', 70, 75),
('Mango', 55, 50);
Creating and inserting values into the FruitInventory table in Snowflake - Snowflake pivot - Snowflake unpivot
Creating and inserting values into the FruitInventory table in Snowflake

2) Using Snowflake UNPIVOT to unpivot the data

Now, lets UNPIVOT it!
To transform the FruitInventory table to have a row for each store's fruit quantity, use the following query:

SELECT FruitName, 
       column_name AS Store,
       Quantity
FROM FruitInventory
UNPIVOT (Quantity FOR column_name IN (StoreA, StoreB)) AS u;
Transforming the FruitInventory table to have a row for each store's fruit quantity - Snowflake pivot - Snowflake unpivot
Transforming the FruitInventory table to have a row for each store's fruit quantity

As you can see, the result will be a table with columns FruitName, Store, and Quantity, showing the quantity of each fruit in each store.

Bonus—Advanced Snowflake Pivot and Unpivot Techniques

There are several more advanced Snowflake pivot and unpivot techniques that can be applied:

1) Dynamic Pivoting using Stored Procedures

In many cases, the pivot column values might not be known in advance. While Snowflake doesn't natively support dynamic pivoting, you can still use stored procedures or scripting outside of Snowflake to dynamically create the PIVOT query based on the distinct values in the dataset.

2) Pivoting on Multiple Aggregates

As we discussed above, while the Snowflake PIVOT function is designed for a single aggregate function, you can use UNION or JOIN operations to combine results from multiple PIVOT operations, each with a different aggregate.

3) Handling NULLs and Defaults

Sometimes, not all pivot column values will have corresponding data. In such cases, you can use the IFNULL or COALESCE functions to handle NULL values and provide default values.

4) Pivoting on String Aggregates

Beyond numerical aggregates like SUM() or AVG(), you can also pivot on string aggregates, such as LISTAGG, to concatenate strings from rows into a single column.

5) Unpivoting Multiple Groups of Columns:

If you have a table with multiple sets of columns that need to be unpivoted into separate rows, you can use multiple UNPIVOT operations in combination with UNION.

6) Filtering During Unpivot

Sometimes, you might not want to unpivot all columns or all values. You can use WHERE clauses in conjunction with UNPIVOT to filter out specific rows or values during the unpivoting process.

7) Unpivoting with Additional Data:

If you need to include additional columns in the result that aren't part of the UNPIVOT operation, you can include them in the SELECT statement, which is very very useful for retaining context or additional data attributes.

8) Handling Aggregations during Unpivot

When a snowflake pivot applies aggregate functions like AVG(), COUNT(), MAX(), MIN(), and SUM(), performing an unpivot will lead to incorrect inflated values. Additional grouping and sums may be required after unpivoting to handle this.

9) Handling NULLs

After unpivoting, you might end up with rows where the value column has NULL values. You can use WHERE clauses to filter out these rows or use functions like IFNULL or COALESCE to provide default values.

Difference between Snowflake PIVOT and UNPIVOT

Feature Snowflake PIVOT Snowflake UNPIVOT
Purpose Converts rows into columns Converts columns into rows
Use case Useful when you have data in a row format and want to see it in a columnar format for better visualization or aggregation Useful when you have data in a column format and want to transform it into a row format
Syntax PIVOT (agg_func(pivot_col) FOR value_col IN (pivot_values)) UNPIVOT (value_col FOR name_col IN (column_list))
Parameters Aggregate func, pivot col, value col, pivot values Value col, name col, column list
Common Application Often used for creating cross-tabular reports where data is presented in a matrix format Often used for normalizing overly wide tables or preparing data for systems that require a long format

Conclusion

Pivoting and unpivoting are integral techniques for Snowflake data transformation and analysis. Snowflake PIVOT and UNPIVOT provide powerful functionality to pivot and unpivot data with simple SQL syntax.

In this article, we covered:

  • Snowflake PIVOT / UNPIVOT syntax and parameters
  • Common use cases for Snowflake PIVOT and UNPIVOT
  • Pivoting/unpivoting example queries
  • Advanced Snowflake Pivot and Unpivot Techniques
  • Difference between Snowflake PIVOT and UNPIVOT

Pivoting and unpivoting data can help you reshape your data stored in tables for analysis and business intelligence needs.


FAQs

What is the primary purpose of the Snowflake PIVOT function in Snowflake?

Snowflake PIVOT function is used to transform data from a row-level format to a columnar format, allowing for a more structured and readable presentation of data.

How does the Snowflake UNPIVOT function differ from Snowflake PIVOT?

Snowflake PIVOT function transforms data from rows to columns, the UNPIVOT function does the opposite—converting data from a columnar format back to a row-level format.

Can you rename the output column names in Snowflake PIVOT?

Yes, if you prefer different output column names than the input or without quotes, you can include the column names in the AS clause.

How do you order the results after using the Snowflake PIVOT function?

After using Snowflake PIVOT function, you can order the results using the ORDER BY clause followed by the desired column name.

Can I use multiple aggregate functions in a Snowflake PIVOT query?

Yes, but it requires structuring your query carefully and might involve multiple Snowflake PIVOT operations.

Is there a performance difference between PIVOT and UNPIVOT?

The performance largely depends on the dataset size and structure. But, both commands are equally optimized for efficiency in Snowflake

How do I retain additional data columns when using the UNPIVOT function?

When using UNPIVOT, you can include additional columns in the SELECT statement to retain them in the result, providing context or additional data attributes.

Pramit Marattha

Technical Content Lead

Pramit is a Technical Content Lead at Chaos Genius.

People who are also involved

Great! You've successfully subscribed.
Great! Next, complete checkout for full access.
Welcome back! You've successfully signed in.
Success! Your account is fully activated, you now have access to all content.