Pivoting and Unpivoting (Snowflake)

Working with tables often means reshaping data — sometimes you need to turn rows into columns, and other times columns back into rows. That’s where PIVOT and UNPIVOT come in.

They can feel a bit confusing at first, but once you see a few examples, it starts to click.

Where Do PIVOT and UNPIVOT Go?

When you’re writing a query, PIVOT and UNPIVOT belong in the FROM clause.

Think of the basic structure like this:

SELECT
FROM
[JOIN]
[PIVOT(...) or UNPIVOT(...)]
WHERE
GROUP BY
HAVING
ORDER BY

So the order you’d write things in is:

• Start with SELECT (what you want to see)
• Point to your table in FROM
• Add JOINs if needed
• Apply PIVOT or UNPIVOT right after the table (or after the join)
• Then continue with WHERE, GROUP BY, etc.

PIVOT: Turning Rows into Columns

Use PIVOT when you want to rotate values from rows into new columns. Because you’re consolidating rows, you also need an aggregation function (SUM, COUNT, AVG, etc.).

Syntax

SELECT *
FROM table_name
PIVOT (
AGG_FUNCTION(column_to_aggregate)
FOR pivot_column IN ('value1', 'value2', ...)
) AS p;

Example

Imagine this table:

Pivoting by region:

SELECT *
FROM sales
PIVOT(
SUM(sales) FOR region IN ('East', 'West')
) AS p;

Result:

Multiple Aggregations

You can include more than one aggregation at the same time:

SELECT *
FROM sales
PIVOT(
SUM(sales), AVG(sales)
FOR region IN ('East', 'West')
) AS p;

Result:

Aliasing Columns

By default, Snowflake generates column names like SUM_East or AVG_West. To make them clearer, you can alias the values in the IN clause and then provide meaningful column names in the final alias:

SELECT product, east_sum, west_avg
FROM sales
PIVOT(
SUM(sales), AVG(sales)
FOR region IN ('East' AS east, 'West' AS west)
) AS p (product, east_sum, east_avg, west_sum, west_avg);

Now the output is easier to read and reference.

UNPIVOT: Turning Columns into Rows

UNPIVOT does the reverse — it takes multiple columns and converts them into rows.

Syntax

SELECT *
FROM table_name
UNPIVOT(
value_column FOR name_column IN (col1, col2, col3, ...)
) AS u;

• value_column → holds the numeric or text values from the original columns
• name_column → holds the names of the original columns

Example

Starting with this table:

Unpivoting:

SELECT *
FROM sales_pivoted
UNPIVOT(
sales_value FOR region IN (east, west)
) AS u;

Result:

Key Things to Remember

• PIVOT = rows → columns (requires an aggregation).
• UNPIVOT = columns → rows (no aggregation needed).
• Inside PIVOT or UNPIVOT, the measure/aggregation comes first, followed by FOR, then the dimension/column you are pivoting on, then IN.
• Always alias the result table (AS p, AS u), otherwise Snowflake will error.
• You can alias column names in IN them to keep things clean.
• In PIVOT, values in the IN list (pivoted values) require quotes (e.g., 'East', 'West'), while in UNPIVOT, values in the IN list (column names) do not (e.g., east, west).

Pivoting and unpivoting can feel awkward the first few times, but once you practice with a few examples, the syntax starts to feel natural. Keep these patterns in mind, and you’ll save yourself from re-learning it every time.