Pivot Function in Snowflake

Are you looking to find how to use the pivot function within the Snowflake cloud data warehouse or maybe you are looking for a solution on how to convert rows to rows to columns using the pivot function in the Snowflake? You may also want to check what could be real-world use case scenarios where you wanted to use the pivot function.  I will also show you what are the different aggregate functions you can use along with the pivot function like SUM, AVG, MAX, MIN and others. I will explain the pivot function with a practical example in this blog post. Let’s don’t waste time, and start our step-by-step guide tutorial to understand and pivot function with examples.

Pivot function can rotate the table from one column (unique values) to multiple columns with a specified aggregate function.

Example: SELECT …


   PIVOT ( <aggregate_function> ( <pivot_column> )

            FOR <value_column> IN ( <pivot_value_1> [ , <pivot_value_2> … ] ) )

  1. Time Travel in Snowflake
  2. Schedule Task in Snowflake
  3. Check History of Task in Snowflake

Let’s understand the syntax of the pivot function in detail

  • Pivot function has to be used within the from clause only. In the case of the subquery, it should be the part of our subquery from clause.
Aggregate_functionAggregate 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_columnIt is the column for which we are doing the aggregation. The aggregate function that will be applied on this column values.
value_columnIt is the column from which we are generating multiple columns so basically, this column get pivotised
pivot_value_NThis contains the list of the column values which will be converted as a column header in the result query
Table 1: Pivot function Syntax details in Snowflake

How to Pivot in Snowflake?

Pivot function in the Snowflake can convert the unique values of one column into multiple columns. While doing this transformation we can also provide some aggregate function and all the values will get populated accordingly.

For example, assume that we have an employee_bonus table where every employee has bonuses associated with a specific department. Now using the pivot table function we will find out the sum of bonuses for each Department

Let’s first create a dummy table employee_bonus for which we are going to use the pivot function.

Employee_bonus table will have a three columns ID, bonus and department name 

SQL query for creating the employee_bonus table in the Snowflake

create or replace table Employee_Bonus (empid int, department_name text, bonus int)
    as select * from values
    (1, 'CS', 10000),
    (1, 'IT', 400 ),
    (2, 'CS', 4500),
    (2, 'IT', 35000);

| status                               |
| Table EMPLOYEE_BONUS successfully created. |

SQL query for pivot table in the  Snowflake

select * 
  from Employee_Bonus
    pivot(SUM(bonus) for department_name in ('CS', 'IT'))
      as p
  order by empid;

| EMPID |  'CS' |  'IT' |
|     1 | 10000 |   400 |
|     2 |  4500 | 35000 |

How to get column name without quotes using pivot function in Snowflake?

To remove the single quotes (‘) from the column names generated by the pivot function we can use the alias or column name using the AS clause.

Example of SQL query in Snowflake to remove quotes from a column in Pivot function

 select * 
  from Employee_Bonus
    pivot(sum(bonus) for department_name in ('CS', 'IT'))
      as E (New_emp_id, CS, IT)
  order by New_emp_id;

| NEW_EMP_ID |    CS |    IT |
|          1 | 10000 |   400 |
|          2 |  4500 | 35000 |

How to pivot multiple columns in Snowflake?

There is no direct way of getting the multiple columns we can only use one aggregate function within a pivot but there is a workaround for pivoting multiple columns in the Snowflake.

We can use Union operation to create the pivot for multiple columns

Pivot multiple columns in Snowflake Example 

        for department_name in ('CS', 'IT')))

SELECT 'Average' AGG_TYPE, * FROM (SELECT * FROM Employee_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 |

How to pivot without aggregate function in Snowflake?

An aggregate function is a mandatory function in the pivot. Hence there is no direct way of pivoting without using the aggregate function. However, we can have a workaround where we can create the pivot function in such a way that the aggregate function will work for each row rather than combining.

Pivot function Snowflake official Documentation Link

Final Thoughts

By this, we have reached the last section of this article where we have discussed a lot of insightful information about the pivot function in the Snowflake. In this article, we have also seen what is the different ways in which we can use the pivot function in the Snowflake. How we can use the pivot function with multiple columns. How we can pivot without quotes in the Snowflake cloud data warehouse. We have also learned how to do workaround for running the pivot function without the aggregate function.

Please share your comments and suggestions in the comment section below and I will try to answer all your queries as time permits.

IFF Function in Snowflake

Leave a Comment