Snowflake
Turn Rows Into Columns

Snowflake PIVOT: Turn Rows Into Columns

Imagine you have a long list of sales data by month and you want to turn that list into an easy-to-read table where each month's sales are in their own column. The PIVOT function in Snowflake allows you to do just that, transforming data from a long "tall" format to a wide format where each unique value in a column becomes a separate column in the output.

What it does

PIVOT takes values from one column in your data and spreads them across multiple columns, so you can see each value as its own feature. This is particularly useful for reports or visualizations where you want to compare categories side by side.

Syntax

Here’s how you set up a PIVOT:


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

where:

  • <aggregate_function>: This is the function used to summarize the data, such as SUM, COUNT, MAX, etc.
  • <pivot_column>: The column whose values you want to summarize.
  • <value_column>: The column that has the values that will become new column headers.
  • <pivot_value_N>: The specific values in the value column that you want to turn into individual columns.

Examples

Transposing monthly data with PIVOT

Suppose you're managing sales data and you have a table where each row represents sales data for a month for each employee. You want to see each month's sales as its own column rather than as a list.

Input

Table: sales_data

employee_idmonthsales
1January500
1February450
2January700
2February650

Snowflake SQL Query


_10
SELECT
_10
employee_id,
_10
jan,
_10
feb
_10
FROM
_10
sales_data PIVOT (SUM(sales) FOR month IN ('January', 'February')) AS pvt (employee_id, jan, feb);

Output

employee_idjanfeb
1500450
2700650

Explanation

  • The PIVOT function in this query creates two new columns: one for January and one for February.
  • It sums up the sales for each month and displays them under the respective new columns.
  • This makes it much easier to compare sales performance by month for each employee directly.
Y42 SQL completion user interface

Your AI copilot for Snowflake SQL

Write production-ready SQL with AI-based suggestions.

Start for free