Snowflake PIVOT & UNPIVOT Command: Syntax, Usage & Practical Examples 101

Imagine you’re staring at a massive table of data—rows and rows of information—and all you want is to flip it around to get a clearer view. Maybe you need to turn those rows into columns, or vice versa, to make sense of the numbers. That’s where Snowflake PIVOT and Snowflake UNPIVOT commands come. They let us transform our data like flipping a pancake, making it much easier to digest. In this blog, we’ll dive into how these commands work and how they can help us simplify our data analysis tasks.

What is Snowflake?

Snowflake can handle massive amounts of data without the headache of managing servers or infrastructure. It scales storage and computing power separately, so you only pay for what you actually use—no more overpaying for idle resources. Whether you’re running complex SQL queries or collaborating with your team, Snowflake makes data storage and analysis easy, fast, and super flexible.

Snowflake’s Integrated Development Environment (IDE) is totally Web-based. Visit XXXXXXXX.us-east-1.snowflakecomputing.com. You’ll be sent to the primary Online GUI, which works as an IDE, where you can begin interacting with your Data Assets after logging in. Each query tab in the Snowflake interface is referred to as a “Worksheet” for simplicity. These “Worksheets,” like the Tab History function, are automatically saved and can be viewed at any time.

Note: Here, XXXXXXXX refers to your Snowflake Account ID, and us-east-1 will vary according to your specified Snowflake region.

What do you Mean by Pivoting & Unpivoting Tables?

• PIVOT is a relational operator that converts data from row to column. PIVOT rotates a table-valued expression by splitting the output into numerous columns based on the unique values from one column in the expression. We can do aggregate operations where we need them by using the PIVOT operator. This process is known as Pivoting. 
• The reverse of the PIVOT relational operator is the UNPIVOT relational operator. The relational operator UNPIVOT converts data from a column to a row-level and this process is known as Unpivoting. 

What is the Need for Snowflake Pivot Tables?

A Pivot Table is a dynamic approach to rapidly summarise vast volumes of data. A Pivot Table can be used to study numerical data in depth and to answer unexpected queries about your data. Snowflake Pivot Tables can be used for the following purposes:

• Numeric Data Subtotaling and Aggregation, Data Summarization by Categories and Subcategories, and Custom Computations and Formulas are all possible.
• Expanding and collapsing data levels to focus your results, as well as diving down to details from the summary data for regions of interest.
• To see different summaries of the Original Data, move rows to columns or columns to rows (or “pivot“).
• You may focus on precisely the Information you want by filtering, sorting, grouping, and conditionally formatting the most useful and intriguing subset of data.
• Creating Online or Printed Reports that are concise, appealing, and annotated.

How to use the Snowflake PIVOT Command?

Snowflake Pivot command Rotates a table by dividing the unique values from one column in the input expression into numerous columns and aggregates the results on any leftover column values when necessary. While altering the Table Name or subquery in a query, it is supplied in the FROM Clause.

The operator supports the built-in aggregate functions AVG, COUNT, MAX, MIN, and SUM.

Snowflake PIVOT can be used to turn a narrow table (for example, empid, month, sales) into a broader table (for example, empid, jan_sales, feb_sales, mar_sales).

A) Syntax

 SELECT ...
FROM ...
   PIVOT ( <aggregate_function> ( <pivot_column> )
            FOR <value_column> IN ( <pivot_value_1> [ , <pivot_value_2> ... ] ) )

[ ... ]

B) Usage Parameters 

The parameters that are associated with Snowflake Pivot commands are:

• <aggregate_function>: The Aggregate Function is used to combine the PIVOT column’s grouped data.
• <pivot_column>: The Source Table or Subquery Column that will be aggregated.
• <value_column>: The Column in the Source Table or Subquery that holds the values that will be used to produce Column Names.
• <pivot_value_N>: A set of values for the pivot column that will be used to pivot the query results into headings.

C) Example Queries 

Pivot around the amount column in the monthly_sales Database with the following structure to sum the total sales per employee for the given months:

create or replace table monthly_sales(empid int, amount int, month text)
    as select * from values
    (1, 10000, 'JAN'),
    (1, 400, 'JAN'),
    (2, 4500, 'JAN'),
    (2, 35000, 'JAN'),
    (1, 5000, 'FEB'),
    (1, 3000, 'FEB'),
    (2, 200, 'FEB'),
    (2, 90500, 'FEB'),
    (1, 6000, 'MAR'),
    (1, 5000, 'MAR'),
    (2, 2500, 'MAR'),
    (2, 9500, 'MAR'),
    (1, 8000, 'APR'),
    (1, 10000, 'APR'),
    (2, 800, 'APR'),
    (2, 4500, 'APR');

D) Outputs 

select * 
  from monthly_sales
    pivot(sum(amount) for month in ('JAN', 'FEB', 'MAR', 'APR'))
      as p
  order by empid;
+-------+-------+-------+-------+-------+
| EMPID | 'JAN' | 'FEB' | 'MAR' | 'APR' |
|-------+-------+-------+-------+-------|
|     1 | 10400 |  8000 | 11000 | 18000 |
|     2 | 39500 | 90700 | 12000 |  5300 |
+-------+-------+-------+-------+-------+

You can add the Column Names in the AS Clause if you want the Column Names to be without quotes or if you want the output to have different Column Names than the input, as seen below:

select * 
  from monthly_sales
    pivot(sum(amount) for month in ('JAN', 'FEB', 'MAR', 'APR'))
      as p (emp_id_renamed, jan, feb, mar, apr)
  order by emp_id_renamed;
+----------------+-------+-------+-------+-------+
| EMP_ID_RENAMED |   JAN |   FEB |   MAR |   APR |
|----------------+-------+-------+-------+-------|
|              1 | 10400 |  8000 | 11000 | 18000 |
|              2 | 39500 | 90700 | 12000 |  5300 |
+----------------+-------+-------+-------+-------+

OR:

select empid as emp_id, "'JAN'" as january, "'FEB'" as february, "'MAR'" as march,
    "'APR'" as april
  from monthly_sales
    pivot(sum(amount) for month in ('JAN', 'FEB', 'MAR', 'APR')) 
      as p
  order by empid;
+--------+---------+----------+-------+-------+
| EMP_ID | JANUARY | FEBRUARY | MARCH | APRIL |
|--------+---------+----------+-------+-------|
|      1 |   10400 |     8000 | 11000 | 18000 |
|      2 |   39500 |    90700 | 12000 |  5300 |
+--------+---------+----------+-------+-------+

How to use the Snowflake UNPIVOT Command?

Transforms columns into rows to rotate a table. UNPIVOT is a relational operator that takes two columns (from a Table or a Subquery) and a list of columns and creates a row for each of the columns in the list. It is supplied after the Table Name or Subquery within the FROM clause of a query. UNPIVOT isn’t exactly the inverse of Snowflake PIVOT because it can’t undo PIVOT’s Aggregations.

This operator can be used to convert a wide table (e.g. empid, jan_sales, feb_sales, mar_sales) into a narrower table (e.g. empid, month, sales).

A) Syntax 

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

[ ... ]

B) Usage Parameters 

• <value_column>: The name of the created column, which will be filled with data from the columns in the Column List.
• <name_column>: The name of the created column, which will be filled with the names of the columns from the Column List.
• <column_list>: The names of the Source Table or Subquery Columns that will be narrowed down to a Single Pivot Column. Name column will be filled with column names, whereas value column will be filled with Column Values. Only actual column names should be included in the Column List,  subqueries should not be included. 

C) Example Queries 

UNPIVOT the separate month columns to provide a single sales value per month for each employee in a given table, monthly_sales, with the following structure and data:

-- example setup
create or replace table monthly_sales(empid int, dept text, jan int, feb int, mar int, april int);

insert into monthly_sales values
    (1, 'electronics', 100, 200, 300, 100),
    (2, 'clothes', 100, 300, 150, 200),
    (3, 'cars', 200, 400, 100, 50);

D) Outputs 

-- UNPIVOT example
select * from monthly_sales
    unpivot(sales for month in (jan, feb, mar, april))
    order by empid;

+-------+-------------+-------+-------+
| EMPID | DEPT        | MONTH | SALES |
|-------+-------------+-------+-------|
|     1 | electronics | JAN   |   100 |
|     1 | electronics | FEB   |   200 |
|     1 | electronics | MAR   |   300 |
|     1 | electronics | APRIL |   100 |
|     2 | clothes     | JAN   |   100 |
|     2 | clothes     | FEB   |   300 |
|     2 | clothes     | MAR   |   150 |
|     2 | clothes     | APRIL |   200 |
|     3 | cars        | JAN   |   200 |
|     3 | cars        | FEB   |   400 |
|     3 | cars        | MAR   |   100 |
|     3 | cars        | APRIL |    50 |
+-------+-------------+-------+-------+

How to use the CASE Statement for Creating Pivots? 

To create a Snowflake Pivot table in most SQL dialects, you’d have to utilize a series of CASE statements:

SELECT 
  <INDEX COLS>, 
  AGGREGATE_FUNCTION(CASE WHEN <CONDITION> THEN <VALUE> END) <COL_1>, 
  AGGREGATE_FUNCTION(CASE WHEN <CONDITION> THEN <VALUE> END) <COL_2>
FROM
  <TABLE>

Let’s look at another example for multiple Snowflake Pivot: 

SELECT TITLE, 
  SUM(CASE WHEN YEAR(START_TIME) = 2018 THEN DURATION END ) / (60 * 60) "2018",
  SUM(CASE WHEN YEAR(START_TIME) = 2019 THEN DURATION END ) / (60 * 60) "2019",
  SUM(CASE WHEN YEAR(START_TIME) = 2020 THEN DURATION END ) / (60 * 60) "2020"
FROM PUBLIC.NETFLIX
WHERE TITLE IN ('Friends', 'GLOW', 'Arrested Development', 'The Good Place')
GROUP BY TITLE

Output of above Query: 

TITLE
GLOW
Friends
The Good Place
Arrested Development
2018
NULL
192.049166667
9.428888889
1.868611111
2019
NULL
78.595555556
2.958888889
2.521111111
2020
15.726388889
58.134166667
9.827777778
0.9927777778

PIVOT is the other function that can be used to create Snowflake Pivot tables. PIVOT is a custom function in Snowflake that simplifies this behavior. Both of these ways have the drawback of requiring you to specify the resulting pivoted columns before you construct the table, which means you can’t have a Dynamic Pivot Table.

What are the Aggregations Supported by Snowflake PIVOT Command?

Syntax	Description
Aggregate_function	Aggregate function would be used to aggregate the values for the pivot column. The possible set of function for the aggregate function will be MAX, MIN and, AVG, COUNT, SUM
Pivot_column	It is the column for which we are doing the aggregation. The aggregate function that will be applied on this column values.
value_column	It is the column from which we are generating multiple columns so basically, this column get pivotised
pivot_value_N	This contains the list of the column values which will be converted as a column header in the result query

How to Pivot Multiple Snowflake Columns?

You can only use one Aggregate function within the Pivot because there is no direct way to obtain many columns, but there is a workaround for pivoting multiple Columns in Snowflake.

To establish a pivot for many columns, you can use the Union procedure. Below is an example of the same.                                            

SELECT 'SUM' AGG_TYPE, * FROM (SELECT * FROM Employee_Bonus
 pivot(
        SUM(bonus)
        for department_name in ('CS', 'IT')))
UNION ALL
 
SELECT 'Average' AGG_TYPE, * FROM (SELECT * FROM Employee_Bonus
 pivot(
        AVG(bonus)
        for department_name in ('CS', 'IT')))
 
 
+----------+-------+--------------+--------------+
| AGG_TYPE | EMPID |     	'CS' |     	'IT' |
|----------+-------+--------------+--------------|
| SUM  	| 	1 | 10000.000000 |   400.000000 |
| SUM  	| 	2 |  4500.000000 | 35000.000000 |
| Average  | 	1 | 10000.000000 |   400.000000 |
| Average  | 	2 |  4500.000000 | 35000.000000 |
+----------+-------+--------------+--------------+

Conclusion

This article has exposed you to the various Snowflake Pivot Tables to help you improve your overall decision-making and experience when trying to make the most out of your data. In case you want to export data from a source of your choice into your desired Database/destination like Snowflake, then Hevo Data is the right choice for you! 

Hevo Data provides its users with a simpler platform for integrating data from 150+ sources for Analysis. It is a No-code Data Pipeline that can help you combine data from multiple sources. You can use it to transfer data from multiple data sources into your Data Warehouses such as Snowflake, Database, or a destination of your choice. It provides you with a consistent and reliable solution to managing data in real-time, ensuring that you always have Analysis-ready data in your desired destination.

FAQ on Snowflake PIVOT & UNPIVOT Command