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 …

FROM …

   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.

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 

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 |
+----------+-------+--------------+--------------+

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.

• For Azure Study material Join Telegram group : Telegram group link:
• Azure Jobs and other updates Follow me on LinkedIn: Azure Updates on LinkedIn
• Azure Tutorial Videos: Videos Link

IFF Function in Snowflake

• Date_Trunc() in Snowflake
• Date_Add() in Snowflake
• Parse_Json() in Snowflake
• Insert() in Snowflake
• Right() in Snowflake
• StartsWith() in Snowflake
• Replace () in Snowflake
• Position() function
• ListAgg() function
• Like() Function
• LikeAll() Function
• Like Any() Function
• Current_Date() Function
• Row_Number() Function
• Cast() Function
• Show() Function
• ISNULL() Function
• Lag() Function
• ILIKE() Function
• DateDiff() Function
• Substring() Function
• ILIKE ANY() Function
• Round() Function
• To_Date() Function
• Concat() Function
• Length() Function
• TRIM(), LTRIM(), RTRIM()
• SPLIT() Function
• Reverse() Function
• Repeat() Function
• Unicode() Function
• IFNULL() Function
• Pivot() Function
• IFF() Function

Deepak Goyal

Deepak Goyal is certified Azure Cloud Solution Architect. He is having around decade and half experience in designing, developing and managing enterprise cloud solutions. He is also Big data certified professional and passionate cloud advocate.