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> … ] ) )
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.
| 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 |
Contents
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
- 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 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.
