How to Pivot and Unpivot Data in SQL

Pivot tables are easy in Excel. Drag a field to the columns area, drag another to rows, pick an aggregation. Thirty seconds.

In SQL, the same operation generates Stack Overflow questions. The syntax is either nonexistent (Postgres has no PIVOT keyword), awkward (SQL Server's PIVOT has bizarre semantics), or surprisingly clean (DuckDB). And unpivoting — turning wide spreadsheet data into a tall format you can actually query — is somehow even less documented.

Here's how to do both, across the databases you're actually using.

The CASE-WHEN pivot (works everywhere)

This is the portable version. It runs on Postgres, MySQL, DuckDB, SQLite, Redshift, BigQuery. You hardcode the column values you want to pivot on and the engine does the rest.

Say you have a regional_sales table with one row per region per quarter:

select
  region,
  sum(case when quarter = 'Q1' then revenue else 0 end) as q1,
  sum(case when quarter = 'Q2' then revenue else 0 end) as q2,
  sum(case when quarter = 'Q3' then revenue else 0 end) as q3,
  sum(case when quarter = 'Q4' then revenue else 0 end) as q4
from regional_sales
group by region
order by region;

That turns rows like ('EMEA', 'Q1', 420000) and ('EMEA', 'Q2', 385000) into a single row: ('EMEA', 420000, 385000, ...). One row per region, one column per quarter.

It works. It's readable. But you typed quarter four times, and if someone adds a value to the data that you didn't account for, your output silently ignores it.

The other gotcha: NULLs. If a region has no revenue in Q3, else 0 gives you a zero. Drop the else clause and you get NULL instead, which breaks downstream SUMs. Pick one approach and be consistent.

Postgres CROSSTAB (tablefunc extension)

Postgres ships a crosstab() function in the tablefunc extension. It's more declarative than CASE-WHEN — you tell it the row, category, and value columns and it generates the pivot.

create extension if not exists tablefunc;
 
select *
from crosstab(
  $$
    select region, quarter, sum(revenue)::int
    from regional_sales
    group by region, quarter
    order by region, quarter
  $$,
  $$ values ('Q1'), ('Q2'), ('Q3'), ('Q4') $$
) as ct(region text, q1 int, q2 int, q3 int, q4 int);

Worth the setup? Sometimes. The inner query must return exactly three columns (row identifier, category, value), ordered by the first two. The second argument lists the category values — yes, you still hardcode them. And that as ct(...) clause where you define the output schema? The types have to match exactly or it fails with an unhelpful cast error.

I reach for CROSSTAB when I'm building a materialized view that gets refreshed on schedule. The query plan is often better than a pile of CASE expressions once you hit 10+ pivot columns. For ad-hoc work, CASE-WHEN is simpler. You don't need to remember the dollar-quoted string syntax, the column ordering requirements, or the mandatory output type definition.

DuckDB PIVOT: how it should work

DuckDB added first-class PIVOT and UNPIVOT as SQL keywords. No extensions, no CASE-WHEN gymnastics.

pivot regional_sales
on quarter
using sum(revenue)
group by region;

Four lines. Same result as the 10-line CASE-WHEN version. DuckDB figures out the distinct values of quarter, creates one column per value, and applies the aggregation. If a new quarter shows up in the data, it shows up in the output automatically.

You can pivot on multiple aggregations too:

pivot regional_sales
on quarter
using sum(revenue) as total_rev, count(*) as num_deals
group by region;

That gives you q1_total_rev, q1_num_deals, q2_total_rev, q2_num_deals — all generated from the data. No manual column definitions.

If you're running DuckDB locally or through Fastero's cross-source DuckDB store, this is the syntax to use. The CASE-WHEN version works in DuckDB too, but there's no reason to write it by hand when the engine handles it natively.

UNPIVOT: wide data to tall

Unpivoting is the inverse, and honestly it's the one you need more often. Someone hands you a spreadsheet export where months are columns:

You can't GROUP BY month. You can't filter to a date range. You can't join this to an actuals table. The data is in the wrong shape for anything analytical.

In DuckDB:

unpivot budget_sheet
on jan_budget, feb_budget, mar_budget, apr_budget
into
  name month
  value budget;

Now you get one row per department per month. You can join to actuals, compute variance, filter by department, aggregate across time.

Postgres has no UNPIVOT keyword. The best workaround is a lateral join with VALUES:

select
  department,
  month_name,
  budget
from budget_sheet
cross join lateral (
  values
    ('jan', jan_budget),
    ('feb', feb_budget),
    ('mar', mar_budget),
    ('apr', apr_budget)
) as unpivoted(month_name, budget);

Verbose, but it works on every Postgres version. The lateral join is faster than a UNION ALL approach because it scans the source table once instead of once per column.

Real use case: Sheets budget vs. actuals on a dashboard

This is a pattern I see constantly. Finance maintains a budget spreadsheet in Google Sheets — months as columns, departments as rows. You need a dashboard widget showing budget vs. actuals by month with variance highlighting.

The spreadsheet gets synced to your analytical store. Fastero pulls Google Sheets as a connection source, so the sheet lands as a queryable table. First move: unpivot it, then join to actuals.

with budget_tall as (
  unpivot sheets_budget
  on columns(* exclude (department, cost_center))
  into
    name month_col
    value planned_spend
),
cleaned as (
  select
    department,
    replace(month_col, '_budget', '') as month_name,
    planned_spend
  from budget_tall
)
select
  c.department,
  c.month_name,
  c.planned_spend,
  coalesce(a.actual_spend, 0) as actual_spend,
  c.planned_spend - coalesce(a.actual_spend, 0) as variance
from cleaned c
left join monthly_actuals a
  on a.department = c.department
  and a.month_name = c.month_name
order by c.department, c.month_name;

The columns(* exclude (...)) syntax in DuckDB's UNPIVOT is worth calling out. You don't list every month column by name — it grabs everything except the columns you exclude. When Finance adds May and June columns to the sheet, your query picks them up without a code change.

That output goes straight into a dashboard widget. Schedule it to refresh daily and you have a live budget-vs-actuals view that updates when either the spreadsheet or the database changes.

Dynamic pivots: the genuinely hard problem

Everything above hardcodes the pivot values. Q1 through Q4. Jan through Apr. That works when you know the values ahead of time.

What if you don't? Product lines that get added monthly. Survey questions that change each quarter. Customer segments that someone redefines on a whim.

SQL makes this hard by design. The standard requires column names and types to be known at parse time — before any data is read. A dynamic pivot needs to read the data first to discover what the columns should be. Chicken-and-egg.

DuckDB's PIVOT solves this at execution time, which is a language extension, not standard SQL. That's why pivot sales on product using sum(revenue) can auto-discover values — the DuckDB parser defers column resolution.

In Postgres, the answer is dynamic SQL inside a PL/pgSQL function: query the distinct values, build a SQL string with one CASE per value, EXECUTE it. It works, but it's a stored procedure, not something you can paste into a BI tool's SQL editor or share with a teammate who doesn't have function-creation privileges.

If your pivot values change regularly, the most practical path is to keep data tall in SQL and let your visualization layer pivot at render time. Most dashboard tools — including the widgets in Fastero — can pivot and group at display time. Your SQL stays a straightforward GROUP BY, and the columns adjust when the data changes.

When to pivot in SQL vs. your dashboard

Pivot in SQL when the wide format is the deliverable: an export to a downstream system, a materialized view with a fixed schema, a report that goes into a slide deck via CSV.

Unpivot in SQL almost always. Wide spreadsheet data needs to become tall before you can do anything useful with it. Do the reshape once, store the result, and every downstream query gets simpler.

For everything in between, keep the data tall and pivot at the presentation layer. Your queries stay portable, your columns stay dynamic, and you don't end up maintaining a PL/pgSQL function that breaks every time someone renames a product line.

Related reading:

• DuckDB as your team's analytics engine
• Connect Google Sheets to dashboards and agents
• Build live dashboards from any SQL query
• Collaborative SQL editor with caching and optimization

Try Fastero free — connect your databases and spreadsheets, write SQL with native DuckDB PIVOT/UNPIVOT, and build dashboards from the results. No credit card required.