Snowflake Data Pivot and Unpivot Operations

Efficient data manipulation is paramount for businesses aiming to derive actionable insights and maintain a competitive edge. Snowflake, with its powerful capabilities, offers robust solutions for data manipulation tasks, especially concerning pivoting and unpivoting data.

Understanding the  Pivot and Unpivot in Snowflake

What is Pivot?

Pivoting data in Snowflake involves rotating rows into columns, enabling a concise representation of complex datasets. This transformation proves invaluable when summarizing, aggregating, or presenting data in a more structured manner. Through pivot operations, disparate information becomes consolidated, facilitating streamlined analysis.

Unpivot in Snowflake: Breaking Down Complex Structures

Conversely, unpivoting data involves the opposite transformation—converting columns into rows. This process is pivotal in breaking down complex structures and expanding condensed information, enabling a more granular examination of datasets. Unpivot operations are crucial when dealing with multi-dimensional data, providing enhanced flexibility for in-depth analysis.

Pivot Example 

Suppose we have a dataset containing sales information with columns for product category, month, and sales amount. Initially, the data might look like this:

Product Category	Month	Sales Amount
Electronics	January	$500
Fashion	January	$700
Electronics	February	$600
Fashion	February	$900

Now, let's pivot this data to transform it into a more concise form, summarizing sales amounts for each product category across different months.

-- Pivoting the Data in Snowflake

SELECT *
FROM (
  SELECT
    "Product Category",
    "Month",
    "Sales Amount"
  FROM your_sales_table
)
PIVOT (
  SUM("Sales Amount")
  FOR "Month" IN ('January', 'February') -- Specify the months here dynamically or statically
)
ORDER BY "Product Category";

sql

 

After pivoting, the transformed data would look like this:

Product Category	January	February
Electronics	$500	$600
Fashion	$700	$900

In this example, the PIVOT function in Snowflake rearranges the data, converting the distinct values in the "Month" column into separate columns, displaying sales amounts for each product category across different months. This pivot operation provides a clearer and more structured view of sales data, facilitating easier analysis and insights extraction.

 

Now if we have to Unpivot

Suppose we have a dataset that has been pivoted previously, showing sales amounts for different product categories across months:

Product Category	January	February
Electronics	$500	$600
Fashion	$700	$900

Now, let's unpivot this data to transform it back to its original form, displaying sales amounts for each product category in different months.

sql
-- Unpivoting the Data in Snowflake

SELECT
  "Product Category",
  "Month",
  "Sales Amount"
FROM your_pivoted_sales_table
UNPIVOT (
  "Sales Amount" FOR "Month" IN ("January", "February") -- Specify the columns to unpivot dynamically or statically
);

 

After unpivoting, the transformed data would resemble the initial format:

Product Category	Month	Sales Amount
Electronics	January	$500
Electronics	February	$600
Fashion	January	$700
Fashion	February	$900

In this example, the UNPIVOT function in Snowflake converts the columns representing different months back into rows, displaying sales amounts for each product category across various months. This unpivot operation restores the original structure of the data, allowing for a more detailed and granular analysis of sales information.