Pivot and Unpivot in SQL: Patterns That Survive a Changing Column Set

The pivot request always arrives the same way. Someone wants a report with one row per customer and one column per product category, and there are eleven categories today. You write eleven CASE expressions, ship it, and three weeks later marketing adds a twelfth category and the report silently omits it.

I have built this table more times than I want to count, in Snowflake, Postgres, BigQuery, and a Redshift cluster that has since been decommissioned. The SQL is easy. The engineering problem is that a pivot converts data into schema, and schema does not change itself.

So this post has two halves. The mechanics — conditional aggregation, native PIVOT, UNPIVOT, and how each one handles missing cells — and then the architectural part, which is knowing when to refuse the pivot and hand back a long table instead.

The short version of my opinion: pivot in the warehouse only when the category list is a fixed, governed set. Otherwise pivot in the BI tool, which was built to regenerate a column list on every render.

Conditional aggregation is the base case

Every pivot in SQL, including the ones written with native PIVOT syntax, compiles to the same shape: group by the row key, aggregate a CASE per output column.

-- One row per customer, one column per category.
select
  customer_id,
  -- SUM with 0 default: absent category reads as 0 revenue.
  sum(case when category = 'electronics' then amount else 0 end) as electronics,
  sum(case when category = 'apparel'     then amount else 0 end) as apparel,
  sum(case when category = 'grocery'     then amount else 0 end) as grocery,
  sum(amount)                                                    as total
from fct_order_lines
group by customer_id;

Two choices in that snippet matter more than they look.

SUM versus MAX. Use SUM when the cell is an additive measure over possibly many rows. Use MAX when the cell holds a single value per key — a status, a latest score, an attribute:

-- Attribute pivot: one value per cell, so MAX, not SUM.
select
  user_id,
  max(case when attribute = 'plan'    then value end) as plan,
  max(case when attribute = 'country' then value end) as country,
  max(case when attribute = 'signup'  then value end) as signup_source
from user_attributes
group by user_id;

MAX over a single non-null value returns that value, and returns NULL when no row matches. That NULL is information: it means the attribute is absent. SUM(... else 0) destroys the distinction between “zero revenue” and “no rows,” which matters the moment someone computes an average across the column.

The ELSE branch. With SUM, else 0 and no ELSE differ only when every row for that key fails the condition — and that is precisely the missing-category case. I write else 0 for additive money columns where zero is the honest answer, and omit ELSE everywhere else so the null survives.

This form works on every engine, reads clearly, and is what I still write in dbt models even on warehouses that have native syntax, because a portable model is one fewer thing to rewrite during a migration.

Native PIVOT, and what it does not buy you

Snowflake, BigQuery, Oracle, and SQL Server ship a PIVOT operator. Postgres does not, though crosstab() from the tablefunc extension approximates it.

-- Snowflake
select *
from (
  select customer_id, category, amount
  from fct_order_lines
)
pivot (sum(amount) for category in ('electronics', 'apparel', 'grocery'))
  as p (customer_id, electronics, apparel, grocery);

-- BigQuery
select *
from (select customer_id, category, amount from fct_order_lines)
pivot (sum(amount) for category in ('electronics', 'apparel', 'grocery'));

Three things to know before you adopt it.

The column list is still hardcoded. Both examples enumerate the categories, exactly like the CASE version. PIVOT is not dynamic; it is shorter.

The inner query must project only the three roles — row key, pivot column, and value. Any extra column you forget to drop becomes part of the implicit grouping, and you get one row per customer per stray column instead of one row per customer. This is the single most common PIVOT bug I see, and it produces plausible output rather than an error.

The plan is identical to conditional aggregation. I have compared profiles on Snowflake for both forms over the same table and got the same operators and the same bytes scanned. Choose on readability, not speed.

Snowflake added PIVOT (... FOR category IN (ANY ORDER BY category)), which resolves the column list at compile time from the data. It is genuinely convenient for exploration. In a dbt model it means your table’s schema can change between runs without a code change, which breaks every downstream consumer that named a column. I do not allow it in production models for that reason.

Unpivot: the direction that scales

Unpivoting turns wide into long — one row per key per attribute. It is the underrated half of this topic, because a long table absorbs new categories without a schema change.

-- Snowflake / Oracle
select customer_id, category, revenue
from wide_customer_revenue
unpivot (revenue for category in (electronics, apparel, grocery));

-- Portable: UNION ALL, works everywhere.
select customer_id, 'electronics' as category, electronics as revenue
from wide_customer_revenue
union all
select customer_id, 'apparel', apparel from wide_customer_revenue
union all
select customer_id, 'grocery', grocery from wide_customer_revenue;

The UNION ALL form scans the source once per branch on most engines, which is the repeated-scan problem from the CTE materialization post. For a wide table with thirty columns that is thirty scans. Two better options exist.

On engines with array and struct support, build the pairs in one pass and flatten:

-- Snowflake: one scan, then a lateral flatten.
select
  w.customer_id,
  f.key::string    as category,
  f.value::number  as revenue
from wide_customer_revenue w,
lateral flatten (
  input => object_construct(
    'electronics', w.electronics,
    'apparel',     w.apparel,
    'grocery',     w.grocery
  )
) f;

-- BigQuery: same idea with an array of structs.
select customer_id, kv.category, kv.revenue
from wide_customer_revenue w,
unnest([
  struct('electronics' as category, w.electronics as revenue),
  struct('apparel',                 w.apparel),
  struct('grocery',                 w.grocery)
]) kv;

One scan, one expansion. Note that object_construct drops null values in Snowflake, so an absent measure disappears rather than producing a null row — usually what you want for sparse attribute tables, occasionally a surprise when you expected a complete grid.

The other option, when the wide table is a staging artifact you control, is to not create it wide in the first place.

When to pivot, and where

Here is the decision I actually apply in design review.

flowchart TD
  req["Request: one column per category"] --> fixed{"Is the category list fixed and governed?"}
  fixed -->|Yes, under 30 values| warehouse["Pivot in the model with conditional aggregation"]
  fixed -->|No, data-driven| bi["Keep long, pivot in the BI layer"]
  bi --> contract["Long table has a stable schema"]
  warehouse --> test["Add a test: unknown category fails the build"]
  fixed -->|Hundreds of values| never["Never pivot. Long table only."]

A pivot turns data into schema. Only do that where a schema change is a reviewable event.

The governed case is real and common: payment statuses, a fixed set of KPI names, days of the week, months. Those change through a process, not through a marketing campaign. Pivot them in the warehouse and add the test described below.

The ungoverned case — product categories, feature flags, campaign names, survey questions — belongs in the BI tool. Tableau, Looker, Power BI, and every notebook library regenerate the column list on every render, which is exactly what a changing category set needs. Handing them a long table is not a compromise; it is the correct interface, and it is the kind of stability a data contract should be describing.

The test that makes a warehouse pivot safe:

-- Fails the build when a category appears that no column covers.
select distinct category
from fct_order_lines
where category not in ('electronics', 'apparel', 'grocery')
  and order_date >= current_date - 7;

Zero rows or the build fails. This is a five-line dbt test and it converts a silent data loss into a loud, actionable failure. Every hardcoded pivot in production should have one; the broader pattern is in the dbt testing strategy post.

Dynamic pivots without dynamic SQL

When someone insists on a warehouse-side dynamic pivot, there are three honest options and one bad one.

Bad option: a stored procedure that queries distinct category, builds a SQL string, and executes it. It works. It also means your table’s schema is a function of yesterday’s data, no downstream consumer can be statically checked, your dbt DAG cannot know the columns, and debugging happens by reading generated strings out of a log.

Option one: generate at compile time, not run time. In dbt, dbt_utils.get_column_values runs a query during compilation and emits a normal model with literal columns. The column set is fixed per deployment and visible in the compiled SQL.

{% set categories = dbt_utils.get_column_values(
     table=ref('dim_categories'), column='category_code'
) %}

select
  customer_id
  {% for c in categories %}
  , sum(case when category = '{{ c }}' then amount else 0 end)
      as revenue_{{ c | lower | replace(' ', '_') }}
  {% endfor %}
from {{ ref('fct_order_lines') }}
group by 1

This is my default when the request is legitimate. A new category changes the schema on the next full deploy, which is a reviewable event with a diff, not a surprise at 3 a.m. Drive it from a governed dimension table rather than select distinct on a fact table, so a typo in raw data cannot add a column.

Option two: a fixed-width slot layout. Reserve top_1 through top_10 columns and rank categories into slots with ROW_NUMBER. The schema never changes; the meaning of each column is data-driven and carried in parallel name columns.

select
  customer_id,
  max(case when rn = 1 then category end) as top_1_category,
  max(case when rn = 1 then amount   end) as top_1_amount,
  max(case when rn = 2 then category end) as top_2_category,
  max(case when rn = 2 then amount   end) as top_2_amount
from (
  select customer_id, category, sum(amount) as amount,
         row_number() over (
           partition by customer_id order by sum(amount) desc, category
         ) as rn
  from fct_order_lines
  group by customer_id, category
)
group by customer_id;

Note the tiebreaker on category in the ORDER BY. Without it, two categories with equal revenue swap slots between runs and your table is non-deterministic — the same failure mode described in the window frames post.

Option three: a semi-structured column. One VARIANT or JSON column holding a category-to-amount map. Stable schema, arbitrary keys, and consumers extract what they need. Good for sparse attribute sets, poor for anything a BI tool needs to aggregate without extra modeling.

Pitfalls

Extra columns in the PIVOT input. Any column not consumed by the pivot joins the implicit GROUP BY, silently multiplying rows. Always project exactly the row key, pivot column, and value in the inner query.

Pivoting a fanned-out join. If a cell has two source rows because of a duplicate dimension key, SUM doubles it and MAX hides one. There is no error, and the pivot makes the grain impossible to see. Check the grain before pivoting — the fan-out post has the assertions.

Using SUM where MAX belongs. For attribute pivots, SUM with else 0 turns “missing” into “zero,” and averages across that column become wrong in a direction nobody notices.

Unpivoting with UNION ALL over a wide table. Thirty branches means up to thirty scans. Use FLATTEN, UNNEST, or a struct array so the source is read once.

Column names generated from raw data. A category containing a space, a slash, or a leading digit produces an unquoted identifier that breaks or, worse, a quoted mixed-case identifier that every downstream query then has to quote. Sanitize and prefix.

No test on the category list. A hardcoded pivot without a completeness check is a data loss bug with a delay fuse. Write the five-line test.

FAQ

Is native PIVOT faster than conditional aggregation?

No. On Snowflake and BigQuery they produce equivalent plans and scan the same bytes. Pick PIVOT when it is shorter and the input projection is easy to keep clean; pick conditional aggregation when you need portability or per-column logic that differs.

How do I handle a category list of a few hundred values?

Do not pivot. A table with 300 columns is unusable in every BI tool, slow to scan, and impossible to evolve. Keep it long and let the consumer aggregate to the handful of categories they care about.

Can I pivot on two columns at once?

Yes, by concatenating them into a single pivot key — category || '_' || channel — but the column count multiplies. Two dimensions with 10 and 5 values is 50 columns. This is usually the signal that the request belongs in the BI layer.

What is the Postgres equivalent of PIVOT?

Conditional aggregation, or crosstab() from the tablefunc extension. I use conditional aggregation: crosstab requires the output column list in the query anyway, needs a specific input ordering, and its error messages are unhelpful.

Should the pivot live in a dbt model or in the BI tool?

If the category list is governed and small, a model — it is computed once and shared. If the list comes from data, the BI tool, because it regenerates columns on every render and no downstream schema breaks when a category appears.

How do I keep pivoted column names stable across runs?

Drive them from a dimension table with an explicit sort, not from select distinct on a fact table. Sanitize to lowercase snake case and reject anything that does not match a safe identifier pattern before it becomes a column name.

What this means for your pipelines

Pivoting is a presentation transform that people keep pushing into the storage layer. The SQL is trivial, which is why the architectural cost gets ignored: every pivoted column is a schema commitment, and a schema derived from data is a schema nobody agreed to.

My defaults, after enough of these: model long, pivot late. Conditional aggregation for governed, fixed lists, with a completeness test that fails the build on an unknown category. Compile-time generation through dbt when the list is legitimately dynamic but must live in the warehouse, driven by a dimension table so a bad raw value cannot add a column. Runtime dynamic SQL essentially never.

For unpivoting, the opposite bias applies — go long aggressively. A key-value table with a stable three-column schema absorbs every new attribute for free, plays well with incremental models, and is the shape most downstream tools want anyway. Most requests for a dynamic pivot are really a request for a long table plus a spreadsheet, and delivering that ends the conversation permanently instead of monthly.