SQL PIVOT / UNPIVOT / CROSSTAB Across Dialects (Postgres, Snowflake, BigQuery, SQL Server)

sql pivot is the single most-searched reshaping keyword in analytics SQL — and the single most dialect-fractured one in 2026. The same "turn a long monthly-sales table into a wide one column per month" ask has four different answers across the four warehouses most data teams live in: Postgres uses tablefunc.crosstab, Snowflake ships a full ANSI PIVOT with a dynamic ANY ORDER BY form, BigQuery ships PIVOT as a table-function on a subquery, and SQL Server ships the OG PIVOT with an alias table and bracketed labels. There is no portable spelling; the interviewer wants to hear you say that out loud in the first sentence.

This guide is the mid-to-senior comparison you wished existed the first time an interviewer asked you to write snowflake pivot on the whiteboard, sketch a sql server pivot for a monthly reporting table, wrangle a bigquery pivot for a survey dataset, run an sql unpivot to normalise a wide extract, or roll a dynamic pivot sql where the column set is unknown at compile time. It walks through the long-to-wide reshape via postgres crosstab and PIVOT keywords (Postgres tablefunc + Snowflake / BigQuery / SQL Server PIVOT), the wide-to-long reshape via UNPIVOT and its dialect equivalents (SQL Server / Snowflake / BigQuery UNPIVOT, Postgres via UNION ALL / VALUES / jsonb_each_text, MySQL emulation), dynamic pivots when the column set is unknown at compile time (PREPARE / EXECUTE, Snowflake ANY ORDER BY, dbt macros, Python-generated SQL), and the universal SUM(CASE WHEN …) plus ANSI FILTER (WHERE …) conditional-aggregation fallback that works in every dialect. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the unpivoting practice library →, rehearse on conditional-aggregation problems →, and sharpen the grouping axis with grouping-sets drills →.

---

On this page

• Why pivot patterns matter in 2026
• Postgres crosstab + Snowflake / BigQuery PIVOT
• UNPIVOT and dialect equivalents
• Dynamic pivots — unknown columns at compile time
• Conditional-aggregation fallback
• Cheat sheet — pivot and unpivot recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why pivot patterns matter in 2026

PIVOT and UNPIVOT are the two halves of table reshaping — every dialect spells them differently

The one-sentence invariant: a sql pivot turns a long-form table (one row per observation) into a wide-form table (one column per observation category), and a sql unpivot reverses the operation — but no two major warehouses spell the operation the same way, so every senior data engineer keeps a mental dialect matrix in their head. Once you internalise that "reshape direction + dialect keyword" is the whole design space, the entire pivot interview surface collapses to a lookup: what's the input shape, what's the target shape, and what's the warehouse?

Four axes interviewers actually probe.

• Direction. Long-to-wide (PIVOT) or wide-to-long (UNPIVOT). Analysts usually want long-to-wide for reporting; ETL pipelines usually want wide-to-long for normalisation. The mental model split is: PIVOT collapses rows into columns; UNPIVOT explodes columns into rows.
• Known vs unknown column set. If the labels are known at compile time (Jan, Feb, Mar), any dialect's PIVOT handles it. If they are unknown at compile time (a new product category can arrive at any time), you need either dynamic pivot sql (PREPARE / EXECUTE), Snowflake's ANY ORDER BY form, or a code-generator (dbt macro, Python + SQLAlchemy).
• Aggregate function. PIVOT requires an aggregate — SUM, MAX, MIN, AVG, COUNT, LIST_AGG. The choice matters: MAX vs SUM produces different answers when there are duplicate rows. Interviewers love to probe "what if two rows share the same (product, month) key?" — the answer depends on the aggregate.
• Portability. SUM(CASE WHEN …) and the ANSI FILTER (WHERE …) clause both work as universal fallbacks, and both plan identically to PIVOT in every modern warehouse. When your team ships one query across Postgres + Snowflake + BigQuery, conditional aggregation is often the right call.

The dialect split — same shape, different keywords.

• Postgres — no native PIVOT keyword; use the tablefunc extension's crosstab(text, text) function, or fall back to SUM(CASE WHEN …). The crosstab function has known ergonomic warts (output column names must be pre-declared, category ordering matters).
• Snowflake — full ANSI PIVOT(agg FOR col IN (v1, v2, …)) since 2019, plus a flagship ANY ORDER BY dynamic form since 2023 that eliminates the need for EXECUTE IMMEDIATE for most cases.
• BigQuery — table-function PIVOT(agg FOR col IN (v1, v2, …)) applied to a subquery. Requires explicit column list; no ANY form.
• SQL Server — the original PIVOT(agg FOR col IN ([v1], [v2], …)) p syntax with an alias table. Labels are [bracketed] because they become identifiers.
• MySQL / MariaDB — no PIVOT keyword at all. Every pivot is either SUM(CASE WHEN …) or a stored procedure that builds dynamic SQL from a metadata query.

When conditional aggregation still wins over PIVOT.

• Mixed aggregates in one row. SUM(CASE WHEN month = 'Jan' THEN sales END) AS jan_sales, COUNT(CASE WHEN month = 'Jan' THEN 1 END) AS jan_orders. PIVOT gives you one aggregate for all columns; CASE WHEN lets you mix SUM, COUNT, MAX, and AVG freely.
• Tiny column sets. If you have 3 pivoted columns, SUM(CASE WHEN …) is 6 lines vs PIVOT's 4 lines and is instantly portable across dialects — no readability loss.
• Dialect portability. One query that works identically on Postgres, Snowflake, BigQuery, and SQL Server. Conditional aggregation is the only mechanism that satisfies that constraint.
• Debuggable. The plan is a plain GROUP BY with a projection list. Every runner explains it the same way. PIVOT in some dialects hides the plan inside a syntactic sugar layer that is harder to read.

Common interview asks.

• "Reshape a monthly sales table (year, month, product, sales) into a wide table with one column per month." — the classic long-to-wide.
• "Given a wide survey table (respondent_id, q1, q2, q3, q4, q5), turn it into a long (respondent_id, question, answer) shape." — the classic wide-to-long.
• "New product categories are added to a source table every week. Write a report that pivots by category — how do you handle the unknown column set?" — the dynamic pivot sql probe.
• "Same reshape, but the pipeline runs on both Snowflake and Postgres. Write one query that works in both." — the portability probe (answer: SUM(CASE WHEN …)).
• "What is the difference between PIVOT in SQL Server and crosstab in Postgres?" — the dialect-comparison probe.

What interviewers listen for.

• Do you say "PIVOT is long-to-wide, UNPIVOT is wide-to-long" in the first sentence? — required answer.
• Do you mention "Postgres has no PIVOT keyword — use crosstab or CASE WHEN" unprompted? — senior signal.
• Do you push back on "just use PIVOT everywhere" with "MySQL and Postgres have no first-class PIVOT"? — senior signal.
• Do you reach for SUM(CASE WHEN …) as the portable fallback? — senior signal.
• Do you know FILTER (WHERE …) as the ANSI cleaner form? — senior signal.

Worked example — same monthly-sales reshape, four dialects

Detailed explanation. The classic reporting ask — turn a long (year, month, product, sales) table into a wide table with one column per month per year — reads identically as an English requirement across every warehouse. The SQL to express it looks wildly different by dialect. Writing the same reshape four ways is the fastest way to build a mental dialect matrix.

Question. Given a sales table with columns (year, month, product, sales), write a query that produces one row per product with columns jan, feb, mar containing the sum of sales for each month. Show the query in Postgres (crosstab), Snowflake, BigQuery, and SQL Server.

Input.

year	month	product	sales
2026	Jan	Widget	100
2026	Feb	Widget	150
2026	Mar	Widget	200
2026	Jan	Gadget	80
2026	Feb	Gadget	90
2026	Mar	Gadget	110

Code.

-- Postgres — tablefunc crosstab (extension required)
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM crosstab(
  $$ SELECT product, month, SUM(sales)::int
     FROM sales
     GROUP BY product, month
     ORDER BY 1, 2 $$,
  $$ VALUES ('Jan'), ('Feb'), ('Mar') $$
) AS ct(product text, jan int, feb int, mar int);

-- Snowflake — native ANSI PIVOT
SELECT product, jan, feb, mar
FROM (
  SELECT product, month, sales FROM sales
)
PIVOT (SUM(sales) FOR month IN ('Jan', 'Feb', 'Mar'))
  AS p (product, jan, feb, mar);

-- BigQuery — table-function PIVOT on a subquery
SELECT *
FROM (
  SELECT product, month, sales FROM sales
)
PIVOT (SUM(sales) FOR month IN ('Jan', 'Feb', 'Mar'));

-- SQL Server — PIVOT with alias table and bracketed labels
SELECT product, [Jan], [Feb], [Mar]
FROM (
  SELECT product, month, sales FROM sales
) src
PIVOT (SUM(sales) FOR month IN ([Jan], [Feb], [Mar])) AS p;

Step-by-step explanation.

1. Postgres's tablefunc.crosstab takes two SQL strings: the first must return three columns (row_key, category, value) and be ORDER BY row_key, category; the second is a VALUES list that pins the category order. The AS ct(…) alias declares the output column names — the ergonomic wart is that you must type them.
2. Snowflake's PIVOT operates on a subquery (or table). Every column of the subquery that is NOT named in FOR or aggregated is treated as an implicit grouping key. ('Jan', 'Feb', 'Mar') is a quoted-string label list — Snowflake infers the column names from those labels.
3. BigQuery's PIVOT is nearly identical to Snowflake's but requires the subquery form (FROM (SELECT …)). The output column names are Jan, Feb, Mar — no bracketing.
4. SQL Server's PIVOT also uses a subquery + alias table, but labels go in [square brackets] because they become identifiers. Any label with a space or a reserved word MUST be bracketed.
5. Every dialect's output is identical: one row per product, three columns per month, sums populated. The interviewer wants to see you write two or three of these from memory.

Output.

product	jan	feb	mar
Gadget	80	90	110
Widget	100	150	200

Rule of thumb. For any pivot in a Snowflake or BigQuery pipeline, reach for native PIVOT first — it is the most readable. For Postgres, use crosstab only if you already have the extension; otherwise SUM(CASE WHEN …) is cleaner. For SQL Server, always bracket the labels even when they don't strictly require it — future you will insert a label with a space and you will be glad.

Worked example — direction reversal (wide-to-long)

Detailed explanation. The mirror ask: given a wide table with one column per month, turn it into a long shape with (product, month, sales). The interviewer often frames this as "unpivot," "normalise," or "stack." Every dialect except MySQL has an UNPIVOT keyword; the shapes differ subtly.

Question. Given a wide sales_wide table with columns (product, jan, feb, mar), write the wide-to-long reshape to (product, month, sales) in Snowflake, BigQuery, SQL Server, and Postgres.

Input.

product	jan	feb	mar
Widget	100	150	200
Gadget	80	90	110

Code.

-- Snowflake — UNPIVOT keyword
SELECT product, month, sales
FROM sales_wide
UNPIVOT (sales FOR month IN (jan, feb, mar));

-- BigQuery — UNPIVOT keyword (same shape)
SELECT product, month, sales
FROM sales_wide
UNPIVOT (sales FOR month IN (jan, feb, mar));

-- SQL Server — UNPIVOT keyword, requires alias table
SELECT product, month, sales
FROM (SELECT product, jan, feb, mar FROM sales_wide) src
UNPIVOT (sales FOR month IN (jan, feb, mar)) AS p;

-- Postgres — no UNPIVOT keyword; use UNION ALL
SELECT product, 'jan' AS month, jan AS sales FROM sales_wide
UNION ALL
SELECT product, 'feb' AS month, feb AS sales FROM sales_wide
UNION ALL
SELECT product, 'mar' AS month, mar AS sales FROM sales_wide;

Step-by-step explanation.

1. Snowflake's UNPIVOT(sales FOR month IN (jan, feb, mar)) reads as: "emit one row per input row per named column; put the column name in month and the value in sales." No aggregate is needed because unpivoting is a 1-to-N explosion.
2. BigQuery's UNPIVOT is syntactically identical. The one option worth knowing is INCLUDE NULLS — by default UNPIVOT drops rows where the unpivoted value is NULL; adding INCLUDE NULLS keeps them.
3. SQL Server also uses UNPIVOT, with an alias table like PIVOT. Labels don't need brackets here because you're using them as column references, not identifier literals.
4. Postgres has no UNPIVOT keyword. The idiomatic fallback is UNION ALL — one SELECT per unpivoted column. This is the most explicit form: you name the month label and the value column at every branch.
5. The output shape is identical in every dialect: (product, month, sales) in long form. UNPIVOT is the reverse operation of PIVOT — apply both and you get back the original table.

Output.

product	month	sales
Widget	jan	100
Widget	feb	150
Widget	mar	200
Gadget	jan	80
Gadget	feb	90
Gadget	mar	110

Rule of thumb. For 3–5 columns to unpivot, UNION ALL is fine even on Snowflake or SQL Server. For 20+ columns, always use UNPIVOT (or Postgres's jsonb_each_text trick) — the boilerplate savings pay for the marginal complexity.

Worked example — the aggregate function matters

Detailed explanation. A common interview trap: your input has multiple rows per (product, month) because the source is a transaction log, not a monthly total. The PIVOT aggregate matters — SUM gives you totals, MAX gives you the largest single transaction, COUNT gives you the number of transactions. The interviewer wants to see you name the aggregate deliberately.

Question. A transactions table has multiple rows per (product, month) — one per transaction. Write a PIVOT that reports monthly sums, then modify it to report monthly transaction counts, then modify it to report the maximum single transaction per month. Use Snowflake syntax.

Input.

product	month	amount
Widget	Jan	30
Widget	Jan	70
Widget	Feb	150
Gadget	Jan	80
Gadget	Feb	40
Gadget	Feb	50

Code.

-- Monthly sum per product
SELECT * FROM (SELECT product, month, amount FROM transactions)
PIVOT (SUM(amount) FOR month IN ('Jan', 'Feb'));

-- Monthly transaction count per product
SELECT * FROM (SELECT product, month, amount FROM transactions)
PIVOT (COUNT(amount) FOR month IN ('Jan', 'Feb'));

-- Monthly max single transaction per product
SELECT * FROM (SELECT product, month, amount FROM transactions)
PIVOT (MAX(amount) FOR month IN ('Jan', 'Feb'));

Step-by-step explanation.

1. SUM(amount) treats the input as monthly totals to be aggregated. Widget in Jan has two rows (30 + 70), so the pivoted 'Jan' column for Widget shows 100.
2. COUNT(amount) counts rows per (product, month). Widget in Jan again has two rows, so the pivoted 'Jan' column for Widget shows 2 (not 100).
3. MAX(amount) picks the largest single value per group. Widget in Jan has rows (30, 70), so the pivoted 'Jan' column for Widget shows 70.
4. The interviewer wants to see you name the aggregate deliberately. "I'm using SUM because we want monthly totals; if we wanted transaction counts, I'd swap to COUNT" is the answer that shows depth.
5. Every dialect's PIVOT requires exactly one aggregate function. To emit both sum and count in the same query, you need conditional aggregation (SUM(CASE WHEN month = 'Jan' THEN amount END) AS jan_sum, COUNT(CASE WHEN month = 'Jan' THEN 1 END) AS jan_count) — which is why the conditional-aggregation fallback is the senior go-to for mixed aggregates.

Output (SUM variant).

product	Jan	Feb
Widget	100	150
Gadget	80	90

Rule of thumb. Always name the aggregate function in the first sentence of your interview answer: "I'll use SUM because we want monthly totals." When the interviewer asks a follow-up ("what if we wanted counts?"), swap SUM to COUNT — the shape doesn't change, only the aggregate does.

Senior interview question on pivot / unpivot mental model

A senior interviewer often opens with: "Walk me through when you reach for PIVOT vs UNPIVOT vs SUM(CASE WHEN …). What is the mental split, and when do you pick one over the others?"

Solution Using a 4-question decision framework

Decision framework — PIVOT / UNPIVOT / conditional aggregation

1. Direction?
   - Long-to-wide  → PIVOT (or SUM CASE WHEN as portable fallback)
   - Wide-to-long  → UNPIVOT (or UNION ALL as portable fallback)

2. Column set known at compile time?
   - Yes → static PIVOT / UNPIVOT
   - No  → dynamic PIVOT (Snowflake ANY ORDER BY, or EXECUTE IMMEDIATE)

3. Single aggregate or mixed?
   - Single (all SUM, all COUNT, all MAX) → PIVOT
   - Mixed (SUM + COUNT + MAX in one row) → conditional aggregation

4. Portability required?
   - One dialect         → native PIVOT / UNPIVOT
   - Multiple dialects   → conditional aggregation (works everywhere)

Step-by-step trace.

Ask	Q1 direction	Q2 known cols	Q3 mixed	Q4 portable	Picked
Monthly sales matrix (Snowflake only)	long-to-wide	yes (12 months)	single SUM	no	Snowflake PIVOT
Monthly matrix (Postgres + Snowflake)	long-to-wide	yes	single SUM	yes	SUM(CASE WHEN …)
Report SUM + COUNT + MAX per month	long-to-wide	yes	mixed	no	conditional aggregation
New product category added weekly	long-to-wide	no	single	no	Snowflake ANY ORDER BY
Wide survey (q1..q10) to long	wide-to-long	yes	n/a	no	UNPIVOT
Wide survey, portable across warehouses	wide-to-long	yes	n/a	yes	UNION ALL

After the 4-question pass, the reshape strategy is unambiguous. The remaining 5% — where multiple answers work — defaults to whatever the team's style guide already picks.

Output:

Strategy	When it wins
Native PIVOT	Single dialect, known columns, single aggregate, best readability
Snowflake ANY ORDER BY	Snowflake, unknown columns, no need to write dynamic SQL
SUM(CASE WHEN …)	Multi-dialect portability, mixed aggregates, tiny column sets
UNPIVOT	Wide-to-long, single dialect, known columns
UNION ALL	Wide-to-long, Postgres or MySQL, or portability required

Why this works — concept by concept:

• Direction is the first split — PIVOT and UNPIVOT are not variants; they are opposite operations. Every reshape either compresses rows into columns (PIVOT) or explodes columns into rows (UNPIVOT). Answering the direction question first eliminates half of the option space.
• Known vs unknown columns — the column set determines whether static SQL is enough or whether you need a code-generation layer. Interviewers care about this because it separates "reads docs" from "designs pipelines that survive schema drift."
• Single vs mixed aggregates — PIVOT gives you one aggregate for every output column; conditional aggregation lets you mix. This is the axis that decides whether the natural syntax is PIVOT or SUM(CASE WHEN …).
• Portability is a hard constraint — one query that runs on Postgres AND Snowflake AND BigQuery cannot use native PIVOT in Postgres. Conditional aggregation is the only mechanism that satisfies "runs everywhere without translation."
• Cost — every strategy plans to the same GROUP BY scan under the hood. The choice is a readability / portability trade-off, not a performance one. Pick the shape that reads best in your codebase's dialect.

SQL
Topic — unpivoting
Unpivot / reshape problems

Practice →

SQL Topic — conditional aggregation Conditional aggregation problems

Practice →

---

2. Postgres crosstab + Snowflake / BigQuery PIVOT

postgres crosstab is the tablefunc form; snowflake pivot and bigquery pivot ship the ANSI PIVOT keyword; sql server pivot is the OG dialect ancestor

The mental model in one line: Postgres has no PIVOT keyword — it exposes the tablefunc.crosstab function; Snowflake, BigQuery, and SQL Server all ship an ANSI-style PIVOT clause, but the label syntax and required aliases differ. Once you say "Postgres uses crosstab, everyone else uses PIVOT with different label syntaxes," the entire long-to-wide interview surface becomes a syntax lookup rather than a design problem.

Postgres tablefunc.crosstab — the two-argument form.

• CREATE EXTENSION IF NOT EXISTS tablefunc; — one-time install per database.
• crosstab('SELECT row_key, category, value FROM t ORDER BY 1, 2', 'VALUES (''a''), (''b''), (''c'')') — first arg produces the long-form input, second arg pins the category order.
• Output column list must be declared in AS ct(row_key type, cat_a type, cat_b type, cat_c type).
• Cardinality guarantee: category order in output = row order in the second VALUES list.
• Common gotcha: the first argument MUST be ORDER BY row_key, category — otherwise crosstab silently returns wrong results.

Postgres tablefunc.crosstab — the single-argument form.

• crosstab('SELECT row_key, category, value FROM t ORDER BY 1, 2') — automatic category detection, but the first N distinct categories in the ordered input become the columns.
• Rarely the right choice — you almost always want to name the categories explicitly to make ordering and missing values deterministic.

Snowflake PIVOT.

• SELECT * FROM t PIVOT(SUM(sales) FOR month IN ('Jan', 'Feb', 'Mar')) — quoted string labels; the output column names are 'Jan', 'Feb', 'Mar'.
• The IN clause supports comma-separated literal values, a subquery (FOR month IN (SELECT DISTINCT month FROM t)), or the flagship ANY ORDER BY month dynamic form (Snowflake exclusive, GA 2023).
• DEFAULT ON NULL (0) option — Snowflake-specific extension that replaces NULL cells with a default value.
• Aliasing: PIVOT(…) AS p (product, jan, feb, mar) renames both the implicit group columns and the pivoted columns.

BigQuery PIVOT.

• SELECT * FROM (SELECT product, month, sales FROM t) PIVOT(SUM(sales) FOR month IN ('Jan', 'Feb', 'Mar')) — mandatory subquery form.
• Output column names are the literal string values without quotes (Jan, Feb, Mar).
• Supports AS alias after each IN value: IN ('Jan' AS jan_2026, 'Feb' AS feb_2026).
• No ANY form — the label list is always static. Dynamic pivots require EXECUTE IMMEDIATE.
• Non-string labels (integers, dates) work but produce numeric column names like 2026, which must be back-ticked in downstream queries.

SQL Server PIVOT.

• SELECT product, [Jan], [Feb] FROM (SELECT product, month, sales FROM t) src PIVOT(SUM(sales) FOR month IN ([Jan], [Feb])) AS p — bracketed labels because they become identifiers.
• Alias table (AS p) is required; the src alias on the inner subquery is required.
• The NULL value is displayed as NULL — no default-value option; use COALESCE on the outer projection.
• Labels with spaces or reserved words MUST be bracketed; even without special characters, brackets are the safe default.

The 4-dialect matrix at a glance.

• Postgres: crosstab function + AS ct(…) declared output types.
• Snowflake: PIVOT(agg FOR col IN ('a', 'b')) + optional ANY ORDER BY for dynamic.
• BigQuery: PIVOT(agg FOR col IN ('a', 'b')) on subquery + no ANY form.
• SQL Server: PIVOT(agg FOR col IN ([a], [b])) AS p + bracketed labels.
• MySQL / MariaDB: no keyword; use SUM(CASE WHEN …) or build dynamic SQL.

Common interview probes on PIVOT syntax.

• "How does Postgres do PIVOT?" — it doesn't; use tablefunc.crosstab or SUM(CASE WHEN …).
• "What is the second argument of crosstab?" — the category order pin list; ensures deterministic column order.
• "What is ANY ORDER BY in Snowflake PIVOT?" — dynamic pivot where the label set is discovered at query time; no dynamic SQL required.
• "Does BigQuery support dynamic PIVOT?" — not natively; use EXECUTE IMMEDIATE on a string-built column list.
• "Why does SQL Server PIVOT need brackets on labels?" — labels become identifiers; brackets escape reserved words and spaces.

Worked example — Postgres tablefunc crosstab, two-argument form

Detailed explanation. The most common postgres crosstab interview question: given a sparse (product, month, sales) table, reshape to a wide monthly matrix. Some product-month cells have no rows — the interviewer wants to know that the two-argument form handles missing cells cleanly, and that you know to ORDER BY 1, 2 in the first argument.

Question. Given a sales table with sparse (product, month, sales) rows, write a Postgres query using tablefunc.crosstab that produces one row per product with columns jan, feb, mar. Explain what happens for missing product-month combinations.

Input.

product	month	sales
Widget	Jan	100
Widget	Mar	200
Gadget	Feb	90

Code.

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM crosstab(
  $$
    SELECT product, month, SUM(sales)::int
    FROM sales
    GROUP BY product, month
    ORDER BY 1, 2
  $$,
  $$ VALUES ('Jan'), ('Feb'), ('Mar') $$
) AS ct (
  product text,
  jan int,
  feb int,
  mar int
);

Step-by-step explanation.

1. The first argument must produce three columns: the row key (product), the category (month), and the value (SUM(sales)). It must be ORDER BY row_key, category — otherwise crosstab may assign values to the wrong output cells.
2. The second argument is a VALUES list that pins the output column order. 'Jan' is column 1, 'Feb' is column 2, 'Mar' is column 3. Categories in the first arg not present in this list are dropped from the output.
3. Missing product-month combinations produce NULL in the output cell. Widget has no February row, so feb is NULL for Widget. To display 0 instead, wrap the outer projection with COALESCE(jan, 0).
4. The AS ct(…) alias declares the output column names AND their types. You cannot omit this — crosstab is a SETOF record function, and Postgres requires explicit type declarations for record-returning functions in the FROM clause.
5. The ::int cast on SUM(sales) matters because SUM returns bigint by default. The output column types in AS ct(…) must match — a mismatch throws structure of query does not match function result type.

Output.

product	jan	feb	mar
Gadget	NULL	90	NULL
Widget	100	NULL	200

Rule of thumb. Always use the two-argument form of crosstab. The one-argument form auto-detects categories from the input, which makes column order non-deterministic and breaks when a category is missing from a batch. Pin the categories explicitly with VALUES (...).

Worked example — Snowflake PIVOT with an inline subquery

Detailed explanation. Snowflake's PIVOT is the cleanest of the four — no alias table required, no bracketing, and the operand can be an inline subquery. The interviewer often uses this to test whether you understand implicit grouping: any column in the input NOT named in FOR and NOT aggregated becomes an implicit GROUP BY key.

Question. Given a orders table with (order_id, product, region, month, amount), write a Snowflake PIVOT that produces monthly sums per (product, region). Explain the implicit grouping and how to avoid an accidental cross-product from extra columns.

Input.

order_id	product	region	month	amount
1	Widget	US	Jan	100
2	Widget	US	Feb	120
3	Widget	EU	Jan	60
4	Gadget	US	Jan	80

Code.

-- Correct — implicit grouping on (product, region)
SELECT * FROM (
  SELECT product, region, month, amount FROM orders
)
PIVOT (SUM(amount) FOR month IN ('Jan', 'Feb'))
  AS p (product, region, jan, feb);

-- WRONG — order_id makes every row unique, defeats the pivot
SELECT * FROM orders
PIVOT (SUM(amount) FOR month IN ('Jan', 'Feb'));

Step-by-step explanation.

1. PIVOT treats every column of the input NOT named in FOR and NOT the aggregated column as an implicit GROUP BY key. In the correct version, product and region are the implicit groups.
2. In the wrong version, order_id is unique per row — so the implicit GROUP BY (order_id, product, region) produces one output row per input row. The pivot degenerates because every group has exactly one (product, region, month) combination.
3. Always project only the columns you want as grouping keys + the pivot column + the value column, using an inner subquery: SELECT product, region, month, amount FROM orders. Never let PIVOT operate on a table with extra columns you don't want as grouping keys.
4. The AS p (product, region, jan, feb) clause renames the implicit and explicit columns in one shot. It is optional — without it, Snowflake keeps the implicit column names and generates 'Jan', 'Feb' as the pivoted column names.
5. To sanity-check, run the subquery first: SELECT product, region, month, amount FROM orders. If those four columns are exactly what you want to pivot, wrap with PIVOT.

Output.

product	region	jan	feb
Gadget	US	80	NULL
Widget	EU	60	NULL
Widget	US	100	120

Rule of thumb. Always use the inline-subquery form of PIVOT. Never point PIVOT at a raw table — the implicit GROUP BY on every column is the most common snowflake pivot bug. The subquery gives you control over exactly which columns become grouping keys.

Worked example — BigQuery PIVOT with column aliases

Detailed explanation. BigQuery's PIVOT produces output column names from the label literals verbatim. 'Jan' becomes a column literally named Jan, which is fine for identifiers but breaks when the label is a number, a date, or contains special characters. The AS alias in the IN list is the fix.

Question. Given an events table with (user_id, year, event_count), write a BigQuery PIVOT that produces one column per year (2024, 2025, 2026). Handle the fact that numeric column names must be back-ticked downstream.

Input.

user_id	year	event_count
u1	2024	10
u1	2025	15
u1	2026	20
u2	2025	8
u2	2026	12

Code.

-- Without alias — numeric column names, requires back-ticks downstream
SELECT * FROM (
  SELECT user_id, year, event_count FROM events
)
PIVOT (SUM(event_count) FOR year IN (2024, 2025, 2026));

-- With alias — human-friendly column names
SELECT * FROM (
  SELECT user_id, year, event_count FROM events
)
PIVOT (SUM(event_count) FOR year IN (
  2024 AS y2024,
  2025 AS y2025,
  2026 AS y2026
));

Step-by-step explanation.

1. Without the AS alias, BigQuery produces columns literally named 2024, 2025, 2026. In subsequent queries, you must back-tick them: SELECT \2024FROM pivoted_table.
2. Back-ticked numeric column names are legal BigQuery but visually noisy and error-prone. Downstream tooling (Looker, dbt) often chokes on them.
3. The AS y2024 alias in the IN clause makes the output column name a plain identifier. This is the recommended pattern for any non-string label (numbers, dates, quoted strings with special characters).
4. The same trick works in Snowflake: IN (2024 AS y2024, 2025 AS y2025). SQL Server doesn't need it because bracket labels are already identifier-safe.
5. Postgres crosstab dodges this whole class by making you declare column names explicitly in AS ct(user_id text, y2024 int, y2025 int, y2026 int) — the trade-off is verbosity in exchange for total control.

Output.

user_id	y2024	y2025	y2026
u1	10	15	20
u2	NULL	8	12

Rule of thumb. Any time your PIVOT labels are not plain string identifiers, alias them with AS. Numeric years, dates, quoted strings with hyphens, and anything else non-alphanumeric will produce hard-to-use column names downstream. The alias costs three characters and buys you a downstream identifier that behaves like every other column.

Worked example — SQL Server PIVOT with COALESCE and bracketed labels

Detailed explanation. SQL Server's PIVOT produces NULL for missing cells and has no built-in default-value option. The idiomatic fix is to wrap the outer projection with COALESCE. The interviewer wants to see you handle NULL cells explicitly.

Question. Given a sales table with (product, month, sales), write a SQL Server PIVOT for the three months (Jan, Feb, Mar) that reports 0 for missing months. Show the alias-table + bracket syntax.

Input.

product	month	sales
Widget	Jan	100
Widget	Feb	150
Gadget	Jan	80

Code.

SELECT
  product,
  COALESCE([Jan], 0) AS jan,
  COALESCE([Feb], 0) AS feb,
  COALESCE([Mar], 0) AS mar
FROM (
  SELECT product, month, sales FROM sales
) src
PIVOT (
  SUM(sales) FOR month IN ([Jan], [Feb], [Mar])
) AS p;

Step-by-step explanation.

1. The inner subquery (src) alias is mandatory in SQL Server PIVOT — the parser requires it.
2. The alias table AS p after the PIVOT clause is also mandatory — SQL Server does not accept an unaliased pivot.
3. Labels in the IN clause are bracketed: [Jan], [Feb], [Mar]. Brackets make them identifier-safe; without them, any label with a space or reserved word would break.
4. Cells with no matching input rows come out as NULL. COALESCE([Jan], 0) in the outer projection replaces NULL with 0. This is the SQL Server-idiomatic way — there is no DEFAULT ON NULL clause like Snowflake.
5. The AS jan on each COALESCE renames the output column from [Jan] (bracketed identifier) to jan (plain identifier). This is optional but recommended — downstream tooling reads plain names more easily.

Output.

product	jan	feb	mar
Gadget	80	0	0
Widget	100	150	0

Rule of thumb. Always wrap sql server pivot output with COALESCE(col, 0) or COALESCE(col, '') on the outer projection when the schema expects zero-instead-of-NULL. Do it in the outer SELECT, not inside the pivot — the pivot itself has no NULL-substitution mechanism.

Senior interview question on Postgres crosstab specifics

A senior interviewer might ask: "You work on Postgres. You have a daily_metrics table with (date, metric_name, value) and you want a wide report with one column per metric. Walk me through the crosstab call and the two common pitfalls people hit."

Solution Using tablefunc.crosstab with explicit category order

-- Postgres tablefunc.crosstab — canonical pattern
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM crosstab(
  $$
    SELECT date::text, metric_name, value
    FROM daily_metrics
    WHERE date >= CURRENT_DATE - INTERVAL '7 days'
    ORDER BY date, metric_name       -- MUST be ordered
  $$,
  $$
    VALUES ('cpu_pct'), ('mem_mb'), ('disk_pct'), ('conn_count')
  $$
) AS ct (
  date text,
  cpu_pct   numeric,
  mem_mb    numeric,
  disk_pct  numeric,
  conn_count numeric
);

Step-by-step trace.

Step	What happens	Why it matters
1	First arg runs, returns rows (date, metric, value) ordered by (date, metric)	crosstab expects the natural row order
2	Second arg establishes column order: cpu_pct, mem_mb, disk_pct, conn_count	Pins output shape; missing metrics → NULL
3	For each distinct date, one output row is emitted	Each row_key becomes one output row
4	Each metric's value lands in its matching column	Cell = the value where metric_name matches the category
5	AS ct(…) declares the column list + types	Postgres requires typed record output for FROM clause functions

The two most common pitfalls interviewers probe:

• Pitfall 1: forgetting ORDER BY 1, 2 in the first argument. Crosstab does NOT re-sort internally; if the input is unordered, values can land in the wrong output cells silently. Always ORDER BY row_key, category in the first argument.
• Pitfall 2: category-name type mismatch. The second-argument VALUES are matched to the category column of the first argument by textual equality. If metric_name is text but you write VALUES (1) (integer), the match fails and every cell is NULL. Cast both sides to text if in doubt.

Output:

date	cpu_pct	mem_mb	disk_pct	conn_count
2026-07-04	55	1200	60	42
2026-07-05	58	1250	61	45
2026-07-06	60	1300	63	44

Why this works — concept by concept:

• Two-argument form pins column order — the VALUES list is the source of truth for which categories appear and in what order. Categories in the input but not the list are dropped; categories in the list but not the input become NULL columns.
• ORDER BY 1, 2 is non-negotiable — crosstab uses the row order to detect row-key boundaries. Skipping the ORDER BY produces silent data corruption. Always sort by (row_key, category) in the first argument.
• AS ct(…) declares record types — Postgres's SETOF record return type requires the caller to name output columns and their types. The types must match the first argument's projection exactly.
• NULL cells are the missing-value contract — if a (date, metric_name) combination has no row, the cell is NULL. Wrap with COALESCE(cpu_pct, 0) in the outer projection when the schema expects zeros.
• Cost — crosstab is a wrapper around the underlying GROUP BY row_key scan plus a hash of categories. Cost is O(rows) plus O(categories) per row_key. Suitable for any reasonable metric-count workload; not a bottleneck.

SQL
Topic — aggregation
Aggregation and pivot problems

Practice →

SQL Topic — case-when CASE WHEN pivot fallback drills

Practice →

---

3. UNPIVOT and dialect equivalents

sql unpivot is the mirror of PIVOT — SQL Server, Snowflake, BigQuery ship the keyword; Postgres and MySQL use UNION ALL or JSON helpers

The mental model in one line: UNPIVOT explodes named columns into (name, value) pairs — SQL Server, Snowflake, and BigQuery ship a first-class UNPIVOT keyword, while Postgres uses UNION ALL, unnest(ARRAY[...]), or jsonb_each_text(row_to_json(t)) to achieve the same shape, and MySQL falls back to UNION ALL or JSON_TABLE. Once you say "wide-to-long has three named implementations and three portable fallbacks," the entire unpivot interview surface becomes a keyword lookup.

SQL Server UNPIVOT — the OG dialect.

• Syntax: SELECT keys, name_col, value_col FROM src UNPIVOT (value_col FOR name_col IN (col1, col2, col3)) AS p.
• Requires an alias table (AS p), same as PIVOT.
• The value_col and name_col are new column names you invent; IN (…) lists the source columns to unpivot.
• All unpivoted columns must have the same data type — SQL Server does not coerce. If your source has int and varchar mixed, cast to a common type in a subquery first.
• Drops NULL cells by default; no INCLUDE NULLS option — use UNION ALL if you need every cell preserved.

Snowflake UNPIVOT.

• Syntax: SELECT * FROM t UNPIVOT (value_col FOR name_col IN (col1, col2, col3)).
• Cleaner than SQL Server — no alias table required.
• INCLUDE NULLS option: UNPIVOT INCLUDE NULLS (value_col FOR name_col IN (…)) keeps NULL cells; default is EXCLUDE NULLS.
• Column aliases in the IN clause: IN (jan AS 'January', feb AS 'February') renames the label values.
• Type coercion is stricter than PIVOT — the unpivoted columns must share a compatible type; mixing NUMBER and VARCHAR errors out.

BigQuery UNPIVOT.

• Syntax matches Snowflake almost line-for-line: SELECT * FROM t UNPIVOT (value_col FOR name_col IN (col1, col2, col3)).
• Also supports INCLUDE NULLS and EXCLUDE NULLS (default).
• The IN clause can also accept groups of columns for multi-value unpivots: IN ((jan_sales, jan_cost) AS 'Jan', (feb_sales, feb_cost) AS 'Feb') — a Snowflake and BigQuery exclusive.
• BigQuery does NOT allow arbitrary subqueries in the IN list (unlike PIVOT's IN); it must be a static column list.

Postgres — three portable equivalents.

• UNION ALL — the classical form. One SELECT per unpivoted column: SELECT keys, 'jan' AS month, jan AS value FROM t UNION ALL SELECT keys, 'feb', feb FROM t …. Verbose for many columns but explicit.
• unnest(ARRAY[...], ARRAY[...]) — the terse form using paired arrays. SELECT keys, m.month, m.value FROM t, unnest(ARRAY['jan','feb','mar'], ARRAY[t.jan, t.feb, t.mar]) AS m(month, value). One row per array element, per input row.
• jsonb_each_text(to_jsonb(t)) — the "unpivot every column" one-liner. Converts each row to a JSON object, then explodes each key-value pair. Great for wide tables where you don't want to type the column names.

MySQL emulation.

• No UNPIVOT keyword. Options: UNION ALL (works on every MySQL version), JSON_TABLE (MySQL 8.0.4+, joins against a JSON array), stored procedure with dynamic SQL.
• The typical answer: UNION ALL for 3-10 columns, JSON_TABLE for wider tables.

When to pick which.

• ≤ 5 columns to unpivot, portability required — UNION ALL. Every dialect. Verbose but bulletproof.
• 5-50 columns, single dialect (SQL Server / Snowflake / BigQuery) — UNPIVOT keyword. Boilerplate savings pay off.
• Postgres, wide table (20+ columns), you want "unpivot everything" — jsonb_each_text(to_jsonb(t)). One line, no column list to maintain.
• BigQuery, multi-value unpivot (jan_sales + jan_cost together) — grouped IN clause. Snowflake and BigQuery exclusive.

Common interview probes on UNPIVOT.

• "How do you UNPIVOT in Postgres?" — Postgres has no keyword; use UNION ALL, unnest(ARRAY[...], ARRAY[...]), or jsonb_each_text(to_jsonb(t)).
• "What is the difference between INCLUDE NULLS and EXCLUDE NULLS?" — EXCLUDE NULLS (default in Snowflake / BigQuery / SQL Server) drops rows where the unpivoted value is NULL; INCLUDE NULLS keeps them.
• "How do you UNPIVOT in MySQL?" — UNION ALL, JSON_TABLE, or a stored procedure with dynamic SQL. No first-class keyword.
• "What is a multi-value unpivot?" — grouping multiple source columns to a single label: Snowflake / BigQuery IN ((jan_sales, jan_cost) AS 'Jan', …). Useful when your wide table has repeated column groups per period.
• "Why must UNPIVOT columns share a type?" — because the output value_col has exactly one type; source columns are stacked into it, and SQL is strict about type unification.

Worked example — SQL Server UNPIVOT with mixed types

Detailed explanation. A wide metrics table has (server_id, cpu_pct decimal, mem_mb bigint, disk_pct decimal). Direct UNPIVOT fails because bigint and decimal don't unify. The interviewer wants to see you cast to a common type in an inner subquery.

Question. Given a wide metrics table with mixed numeric types, write a SQL Server UNPIVOT that produces (server_id, metric_name, value) in long form. Handle the type-mismatch error.

Input.

server_id	cpu_pct	mem_mb	disk_pct
s1	55.0	8192	60.5
s2	62.0	16384	45.2

Code.

-- BROKEN — mixed types cause "operand type clash" error
-- SELECT server_id, metric_name, value
-- FROM metrics
-- UNPIVOT (value FOR metric_name IN (cpu_pct, mem_mb, disk_pct)) AS p;

-- Fixed — cast all source columns to a common type in inner subquery
SELECT server_id, metric_name, value
FROM (
  SELECT server_id,
         CAST(cpu_pct  AS decimal(18,4)) AS cpu_pct,
         CAST(mem_mb   AS decimal(18,4)) AS mem_mb,
         CAST(disk_pct AS decimal(18,4)) AS disk_pct
  FROM metrics
) src
UNPIVOT (value FOR metric_name IN (cpu_pct, mem_mb, disk_pct)) AS p;

Step-by-step explanation.

1. UNPIVOT requires every column in the IN list to have the same data type — the resulting value column can only be one type.
2. Without the inner cast, SQL Server throws The type of column "cpu_pct" conflicts with the type of other columns specified in the UNPIVOT list. The error message is unusually clear here.
3. The inner subquery casts all three source columns to decimal(18,4) — a common type that holds both the integer mem_mb and the decimal cpu_pct / disk_pct values without loss.
4. UNPIVOT (value FOR metric_name IN (…)) reads: "emit one row per input row per named column; put the column name as text in metric_name and the column value in value."
5. NULL cells are dropped silently (no INCLUDE NULLS in SQL Server). If a server has NULL disk_pct, that row is missing from the output — you have to add UNION ALL for the missing rows if you need them.

Output.

server_id	metric_name	value
s1	cpu_pct	55.0000
s1	mem_mb	8192.0000
s1	disk_pct	60.5000
s2	cpu_pct	62.0000
s2	mem_mb	16384.0000
s2	disk_pct	45.2000

Rule of thumb. Whenever the columns you want to UNPIVOT don't share a type, cast them to a common one in an inner subquery. decimal(18,4) is a safe superset for most numeric mixes; varchar(4000) or nvarchar(max) for text mixes.

Worked example — Snowflake UNPIVOT with INCLUDE NULLS

Detailed explanation. A wide attendance table records student attendance across 5 days. Some cells are NULL because the student wasn't enrolled that day. The interviewer wants a long-form view where NULL rows are preserved (so the count of enrolled students matches).

Question. Given a wide attendance table with (student_id, mon, tue, wed, thu, fri), write a Snowflake UNPIVOT that preserves NULL cells. Explain when to use INCLUDE NULLS vs EXCLUDE NULLS.

Input.

student_id	mon	tue	wed	thu	fri
s1	present	present	absent	present	present
s2	NULL	present	present	present	NULL

Code.

-- Default EXCLUDE NULLS — drops NULL cells
SELECT student_id, day, status
FROM attendance
UNPIVOT (status FOR day IN (mon, tue, wed, thu, fri));

-- INCLUDE NULLS — preserves every cell, useful for enrolment analysis
SELECT student_id, day, status
FROM attendance
UNPIVOT INCLUDE NULLS (status FOR day IN (mon, tue, wed, thu, fri));

Step-by-step explanation.

1. Default UNPIVOT behavior in Snowflake is EXCLUDE NULLS. Cells where the source column is NULL do not produce a row in the output.
2. INCLUDE NULLS reverses that — every (student_id, day) pair produces a row, with status = NULL if the source cell was NULL.
3. When to use EXCLUDE NULLS: analytics workloads where NULL means "not applicable" and shouldn't inflate row counts. Attendance-percentage calculations often use this to exclude non-enrolled days.
4. When to use INCLUDE NULLS: audit workloads where you need to know every source cell was considered. Enrolment-vs-attendance reports need this to differentiate "not enrolled" from "absent."
5. BigQuery has identical INCLUDE NULLS / EXCLUDE NULLS semantics. SQL Server does not — it always excludes; use UNION ALL for the include case.

Output (INCLUDE NULLS variant).

student_id	day	status
s1	mon	present
s1	tue	present
s1	wed	absent
s1	thu	present
s1	fri	present
s2	mon	NULL
s2	tue	present
s2	wed	present
s2	thu	present
s2	fri	NULL

Rule of thumb. Name the NULL-handling explicitly in every Snowflake UNPIVOT — either INCLUDE NULLS or EXCLUDE NULLS. Relying on the default is a bug magnet when a downstream query counts rows and misses the NULL cases.

Worked example — Postgres jsonb_each_text for wide-table unpivot

Detailed explanation. A wide daily_stats table has 30 metric columns. Typing them into a UNION ALL would be 30 branches. Postgres has a one-liner: convert each row to a JSON object with to_jsonb(t), then explode each key-value pair with jsonb_each_text. Zero column-name maintenance.

Question. Given a wide daily_stats table with columns (date, m1, m2, m3, m4, m5, …), write a Postgres query that produces long-form (date, metric_name, value) without listing every column. Explain the mechanics of to_jsonb + jsonb_each_text.

Input.

date	m1	m2	m3
2026-07-01	10	20	30
2026-07-02	15	25	35

Code.

SELECT date,
       key   AS metric_name,
       value AS value_text
FROM daily_stats t,
     LATERAL jsonb_each_text(to_jsonb(t) - 'date') AS kv (key, value);

Step-by-step explanation.

1. to_jsonb(t) converts the entire row t into a JSON object where each column name is a key and each value is the JSON representation of the cell. For row ('2026-07-01', 10, 20, 30), it produces {"date": "2026-07-01", "m1": 10, "m2": 20, "m3": 30}.
2. The - 'date' operator removes the date key from the JSON object so the date doesn't end up as a metric row. Standard set-minus for jsonb.
3. jsonb_each_text(json_obj) returns a set of (key text, value text) rows, one per key-value pair in the object. For the row above, it emits (m1, 10), (m2, 20), (m3, 30).
4. LATERAL allows the function to reference the outer row t — required whenever a table-returning function depends on the current row. Without LATERAL, Postgres wouldn't allow t inside jsonb_each_text(to_jsonb(t)).
5. The output is long-form (date, metric_name, value_text) for every metric column in the row. Adding a new metric column to daily_stats requires zero changes to this query — that's the whole reason to use this pattern.

Output.

date	metric_name	value_text
2026-07-01	m1	10
2026-07-01	m2	20
2026-07-01	m3	30
2026-07-02	m1	15
2026-07-02	m2	25
2026-07-02	m3	35

Rule of thumb. For any Postgres unpivot with more than 5 columns, reach for jsonb_each_text(to_jsonb(t) - 'excluded_col'). It's declarative, requires zero column-name maintenance, and survives schema changes. Cast the value back to the target type (value::int, value::numeric) in an outer projection if you need numeric aggregation.

Worked example — Postgres unnest for a fixed column set

Detailed explanation. When you know the exact columns to unpivot but don't want the UNION ALL boilerplate, Postgres's paired-array unnest is the terse form. It stays declarative and produces a compact SQL that reads well.

Question. Given a weekly_sales table with (product, mon, tue, wed, thu, fri), write a Postgres query using unnest(ARRAY[...], ARRAY[...]) that produces (product, day, sales) in long form.

Input.

product	mon	tue	wed	thu	fri
Widget	10	20	15	25	30
Gadget	5	15	10	20	25

Code.

SELECT t.product, d.day, d.sales
FROM weekly_sales t,
     LATERAL unnest(
       ARRAY['mon', 'tue', 'wed', 'thu', 'fri'],
       ARRAY[t.mon, t.tue, t.wed, t.thu, t.fri]
     ) AS d (day, sales);

Step-by-step explanation.

1. unnest(ARRAY[...], ARRAY[...]) is a two-argument variant that pairs elements: element 1 of the first array pairs with element 1 of the second, and so on. The arrays MUST have the same length.
2. The first array holds the day labels ('mon', 'tue', …). The second holds the values from the current row (t.mon, t.tue, …).
3. AS d (day, sales) names the two output columns from the paired unnest.
4. LATERAL is required because the second array references t — the outer row. Postgres executes unnest once per outer row, producing 5 rows per input row (one per day label).
5. Compared to the UNION ALL variant, this is one SELECT statement instead of five. Compared to jsonb_each_text, it's more explicit about the label set — great when you want to name and order the categories.

Output.

product	day	sales
Widget	mon	10
Widget	tue	20
Widget	wed	15
Widget	thu	25
Widget	fri	30
Gadget	mon	5
Gadget	tue	15
Gadget	wed	10
Gadget	thu	20
Gadget	fri	25

Rule of thumb. Use unnest(ARRAY[...], ARRAY[...]) when the label list is small and fixed and you want a single-statement Postgres unpivot. Use jsonb_each_text(to_jsonb(t)) when the column set is large or evolving. Both are more idiomatic than UNION ALL for anything beyond 3-4 columns.

Senior interview question on portable UNPIVOT

A senior interviewer might ask: "You're writing a data-pipeline library that needs to work on Postgres, MySQL, Snowflake, and SQL Server. Write a wide-to-long reshape that runs identically on all four. What syntax do you pick and why?"

Solution Using UNION ALL as the portable canonical form

-- Portable wide-to-long — works on every dialect
SELECT product, 'jan' AS month, jan AS sales FROM sales_wide
UNION ALL
SELECT product, 'feb' AS month, feb AS sales FROM sales_wide
UNION ALL
SELECT product, 'mar' AS month, mar AS sales FROM sales_wide
UNION ALL
SELECT product, 'apr' AS month, apr AS sales FROM sales_wide;

-- For "include NULLs" everywhere, don't filter — WHERE sales IS NOT NULL
-- if you want the drop-nulls behavior of default UNPIVOT.

Step-by-step trace.

Step	What runs	Output
1	First SELECT emits (product, 'jan', jan) for every row	N rows for Jan
2	UNION ALL appends without dedup	N rows preserved
3	Second SELECT emits (product, 'feb', feb)	N rows for Feb
4	Repeat for every month	4×N rows total
5	Optionally WHERE sales IS NOT NULL on outer	Emulates EXCLUDE NULLS

The pattern trades N branches of typing for total portability. For 4 columns it's a 4-branch query; for 30 columns it's a 30-branch query — at that point, you build the query in Python or dbt instead of writing it by hand.

Output:

product	month	sales
Widget	jan	100
Widget	feb	150
Widget	mar	200
Widget	apr	175
Gadget	jan	80
Gadget	feb	90
Gadget	mar	110
Gadget	apr	95

Why this works — concept by concept:

• UNION ALL is the ANSI SQL-92 fallback — every dialect since the 90s supports it. If your query needs to run on Postgres AND MySQL AND SQL Server AND Snowflake, UNION ALL is the only mechanism guaranteed to work.
• One SELECT per unpivoted column — the syntax is verbose but explicit. Every column is named, every label is named, every projection is explicit. Code reviewers can spot missing columns immediately.
• WHERE clauses on outer SELECT emulate NULL-handling — wrap the whole UNION ALL in a subquery and add WHERE sales IS NOT NULL to emulate EXCLUDE NULLS. Omit the filter for INCLUDE NULLS behavior.
• Code-generation friendly — if you have 30 columns, a 10-line Python loop or a dbt Jinja for block writes the 30 branches. The generated SQL is boring and readable.
• Cost — every branch scans the table once. Cost is O(N × rows) where N is the branch count. For a 30-column unpivot on a 100M row table, that's 3B row-touches — at that scale, switch to warehouse-native UNPIVOT or materialise the long form once.

SQL
Topic — unpivoting
UNION ALL unpivot problems

Practice →

SQL Topic — case-expression CASE expression reshape drills

Practice →

---

4. Dynamic pivots — unknown columns at compile time

dynamic pivot sql — Snowflake ANY ORDER BY, PREPARE / EXECUTE elsewhere, dbt macros for the analytics layer

The mental model in one line: a dynamic pivot happens when the label set is unknown at query-authoring time (new product categories, unknown month range, custom user attributes) — Snowflake handles it declaratively with IN (ANY ORDER BY col), everyone else builds a SQL string from a metadata query and runs it with PREPARE / EXECUTE or EXECUTE IMMEDIATE, and analytics engineers side-step the whole class with dbt macros or Python code generators. Once you say "know the labels? static PIVOT. Don't know them? dynamic answer differs by dialect," the entire dynamic-pivot interview surface collapses to a four-way pattern selection.

The dynamic-pivot problem statement.

• Static pivot: PIVOT(SUM(sales) FOR month IN ('Jan', 'Feb', 'Mar')). The labels are hard-coded.
• Dynamic pivot: PIVOT(SUM(sales) FOR month IN (?)) where ? is filled from SELECT DISTINCT month FROM sales. A new month next week? The pivot picks it up automatically.
• The general recipe (non-Snowflake): (1) query the metadata for distinct labels, (2) build the pivot SQL string with the labels interpolated, (3) execute the string.

Snowflake ANY ORDER BY — the flagship dynamic form.

• Syntax: SELECT * FROM t PIVOT(SUM(sales) FOR month IN (ANY ORDER BY month)).
• ANY tells Snowflake: "discover the label set at query time from the input."
• ORDER BY month pins the column order in the output. Without it, order is undefined.
• Works for any label type (string, number, date). Output columns are named exactly as the label values.
• No EXECUTE IMMEDIATE required — this is Snowflake's declarative dynamic-pivot answer, GA in 2023.

SQL Server dynamic pivot with PREPARE / EXECUTE.

• Build a comma-separated bracketed-label string with STRING_AGG(QUOTENAME(month), ',').
• Concatenate into a full PIVOT SQL string.
• Run with EXEC(@sql) or sp_executesql @sql.
• The classic three-step: metadata → string build → execute.

BigQuery EXECUTE IMMEDIATE for dynamic pivot.

• Same pattern as SQL Server: build the label string, concatenate into a pivot statement, run with EXECUTE IMMEDIATE @sql USING @params.
• BigQuery has no ANY form; you must build the string.
• The @params USING clause supports parameter binding for safety.

dbt macro pattern for analytics engineers.

• At dbt compile time, run a run_query('SELECT DISTINCT month FROM {{ ref("sales") }}') inside a Jinja block.
• Loop over the returned values to emit SUM(CASE WHEN month = '{{ col }}' THEN sales END) AS {{ col }} for each label.
• The compiled SQL is static from the warehouse's perspective — no EXECUTE IMMEDIATE needed.
• This is the analytics-engineer canonical answer because it works on every dialect.

Python-generated SQL.

• When the analytics-engineer answer doesn't fit (out-of-cycle report, one-off query), a small Python script queries the labels, builds the SQL, and runs it against the warehouse.
• pandas.DataFrame.pivot_table does the pivot client-side after pd.read_sql; useful when the pivot is downstream of a small warehouse query.
• SQLAlchemy's Query API lets you build the pivot expression programmatically for stronger typing.

Common interview probes on dynamic pivots.

• "How do you handle a pivot when the column set is unknown at compile time?" — Snowflake: ANY ORDER BY; SQL Server: PREPARE + EXECUTE; BigQuery: EXECUTE IMMEDIATE; dbt: Jinja {% for %} macro; else: build the SQL in Python.
• "What is ANY ORDER BY in Snowflake?" — the declarative dynamic-pivot form; auto-discovers labels, produces one column per distinct label.
• "How do you avoid SQL injection in dynamic pivots?" — use QUOTENAME in SQL Server, format('%I') in Postgres, EXECUTE IMMEDIATE … USING @params in BigQuery. Never string-concatenate untrusted input.
• "When would you use a dbt macro instead of dynamic SQL?" — every time you can. Compiled SQL is static, cacheable, and testable; dynamic SQL is opaque to the query planner.
• "What's the failure mode of dynamic pivots?" — a new label the day the report runs causes a schema drift downstream (dashboards break, dbt tests fail, BI models need updates). Always document the discovered label set.

Worked example — Snowflake ANY ORDER BY for a growing product catalog

Detailed explanation. A sales table grows new product categories weekly. Static PIVOT would need updating every week; ANY ORDER BY discovers the label set at query time. This is the pattern to reach for on Snowflake — it's declarative, fast, and requires zero dynamic SQL.

Question. Given a sales(month, product, amount) table where new products are added over time, write a Snowflake query that produces a monthly matrix with one column per product, discovered at query time. Show the ANY ORDER BY syntax and its trade-offs.

Input.

month	product	amount
Jan	Widget	100
Jan	Gadget	80
Jan	Gizmo	50
Feb	Widget	150
Feb	Gadget	90
Feb	Gizmo	60
Feb	Doohickey	40

Code.

-- Snowflake dynamic pivot — ANY ORDER BY, no dynamic SQL
SELECT * FROM (
  SELECT month, product, amount FROM sales
)
PIVOT (SUM(amount) FOR product IN (ANY ORDER BY product));

Step-by-step explanation.

1. The subquery SELECT month, product, amount FROM sales establishes the input shape. month becomes the implicit grouping key.
2. PIVOT (SUM(amount) FOR product IN (ANY ORDER BY product)) reads: "pivot on the product column; discover every distinct product value; sort the output columns alphabetically by product."
3. ANY triggers Snowflake's dynamic-label discovery. At execution time, Snowflake scans the input to find distinct product values, then produces one output column per value.
4. ORDER BY product pins the output column order to alphabetical by product name. Without it, the order is undefined and could change between runs — that's a downstream-breaking bug waiting to happen.
5. When a new product (e.g., 'Doohickey') appears in Feb, it automatically gets a column in the output. Old months without that product show NULL in the new column. Downstream consumers must handle schema drift explicitly.

Output.

month	Doohickey	Gadget	Gizmo	Widget
Feb	40	90	60	150
Jan	NULL	80	50	100

Rule of thumb. On Snowflake, always use ANY ORDER BY <col> for dynamic pivots. Skip EXECUTE IMMEDIATE entirely — the declarative form is faster, testable, and can't be SQL-injected. Warn downstream consumers about schema drift when new labels can appear.

Worked example — SQL Server dynamic pivot with PREPARE / EXECUTE

Detailed explanation. SQL Server has no ANY form. The canonical pattern is three-step: query distinct labels, build a comma-separated bracketed list with STRING_AGG(QUOTENAME(col), ','), and execute the concatenated pivot string.

Question. Given a SQL Server sales(month, product, amount) table, write a dynamic pivot that discovers products at query time. Show the metadata query, string build, and execution steps.

Input.

month	product	amount
Jan	Widget	100
Jan	Gadget	80
Feb	Widget	150
Feb	Gadget	90
Feb	Gizmo	60

Code.

-- Step 1 — build the bracketed label list from a metadata query
DECLARE @cols NVARCHAR(MAX);

SELECT @cols = STRING_AGG(QUOTENAME(product), ',')
FROM (SELECT DISTINCT product FROM sales) AS d;
-- @cols now = '[Gadget],[Gizmo],[Widget]'

-- Step 2 — build the full pivot SQL string
DECLARE @sql NVARCHAR(MAX);

SET @sql =
  N'SELECT month, ' + @cols + N'
    FROM (SELECT month, product, amount FROM sales) src
    PIVOT (SUM(amount) FOR product IN (' + @cols + N')) AS p;';

-- Step 3 — execute the dynamic SQL
EXEC sp_executesql @sql;

Step-by-step explanation.

1. STRING_AGG(QUOTENAME(product), ',') produces the comma-separated bracketed label list. QUOTENAME wraps each label in brackets AND handles any special characters (spaces, reserved words) — critical for SQL injection safety.
2. The SELECT DISTINCT product FROM sales inside the subquery is the metadata query; it produces one row per unique product. STRING_AGG collapses those rows into a single string.
3. The @sql variable is built by string concatenation: the outer projection lists the bracketed labels, and the PIVOT clause repeats them in the IN list. Note that @cols is used twice.
4. sp_executesql @sql is preferred over EXEC(@sql) because it caches the query plan and supports parameter binding for further safety. For this specific case, the plan cache benefit is small because each run's @sql string is different by design.
5. To wrap this in a stored procedure for reuse, parameterise the aggregate function and the source table name. Use sp_executesql @sql, N'@table NVARCHAR(128)', @table = 'sales' for parameter binding.

Output.

month	Gadget	Gizmo	Widget
Jan	80	NULL	100
Feb	90	60	150

Rule of thumb. Always use QUOTENAME in STRING_AGG to build the label list — it's the SQL-injection-safe way to interpolate identifiers. Never string-concatenate raw user input into dynamic pivot SQL; a hostile category name like 'Widget]) SELECT * FROM users; --' would break the query without proper escaping.

Worked example — BigQuery EXECUTE IMMEDIATE dynamic pivot

Detailed explanation. BigQuery has no ANY form and requires the string-build approach. EXECUTE IMMEDIATE is the mechanism; the label discovery uses STRING_AGG inside a scripting block.

Question. Given a BigQuery sales(month, product, amount) table, write a dynamic pivot that discovers products at query time. Show the scripting block that builds and executes the pivot.

Input.

month	product	amount
Jan	Widget	100
Feb	Widget	150
Feb	Gadget	90

Code.

-- BigQuery scripting block — dynamic pivot with EXECUTE IMMEDIATE
DECLARE cols STRING;
DECLARE sql STRING;

-- Step 1 — build the label list
SET cols = (
  SELECT STRING_AGG(DISTINCT CONCAT("'", product, "'") ORDER BY product)
  FROM `project.dataset.sales`
);
-- cols now = "'Gadget','Widget'"

-- Step 2 — build the pivot statement
SET sql = FORMAT("""
  SELECT * FROM (
    SELECT month, product, amount FROM `project.dataset.sales`
  )
  PIVOT (SUM(amount) FOR product IN (%s))
""", cols);

-- Step 3 — run it
EXECUTE IMMEDIATE sql;

Step-by-step explanation.

1. DECLARE introduces script-level variables. cols will hold the label string; sql will hold the full pivot statement.
2. STRING_AGG(DISTINCT CONCAT("'", product, "'") ORDER BY product) builds a quoted-string list 'Gadget','Widget'. The DISTINCT deduplicates; the ORDER BY fixes column order.
3. FORMAT("... %s ...", cols) is BigQuery's printf-style string interpolation. Cleaner than string concatenation with ||.
4. EXECUTE IMMEDIATE sql runs the built SQL. The result set is emitted as if you'd written the pivot statically.
5. For parameterised queries with user input, use EXECUTE IMMEDIATE sql USING @param1, @param2 — BigQuery's binding syntax that avoids injection.

Output.

month	Gadget	Widget
Jan	NULL	100
Feb	90	150

Rule of thumb. On BigQuery, wrap dynamic pivots in a scripting block with DECLARE + EXECUTE IMMEDIATE. For labels sourced from user input, always use the USING @param binding form — string concatenation is the classic SQL-injection vector.

Worked example — dbt macro pivot for analytics engineers

Detailed explanation. In an analytics-engineering workflow, dbt compiles Jinja to SQL at build time. A pivot macro can query the label list at compile time, then emit static SUM(CASE WHEN …) expressions for each label. The compiled SQL is portable across every dialect that supports basic SUM and CASE WHEN.

Question. Write a dbt macro pivot(source_model, column, pivot_column, agg) that emits a static pivot at compile time. Show its use for a monthly-sales report.

Input. The sales model has (month, product, amount). The macro will produce one column per distinct product.

Code.

-- macros/pivot.sql
{% macro pivot(source_model, column, pivot_column, agg='sum') %}
  {% set labels_query %}
    select distinct {{ pivot_column }} as label
    from {{ ref(source_model) }}
    order by 1
  {% endset %}
  {% set labels = run_query(labels_query).columns[0].values() %}

  {% for label in labels %}
    {{ agg }}(case when {{ pivot_column }} = '{{ label }}' then {{ column }} end)
      as {{ label | replace(' ', '_') | lower }}
    {%- if not loop.last -%},{%- endif %}
  {% endfor %}
{% endmacro %}

-- models/monthly_pivot.sql
select
  month,
  {{ pivot('sales', 'amount', 'product') }}
from {{ ref('sales') }}
group by month
order by month;

Step-by-step explanation.

1. The pivot macro takes four arguments: the source model name, the value column, the label column, and the aggregate function (defaulting to sum).
2. At compile time, run_query() executes the labels_query against the warehouse to fetch every distinct product. This is a one-shot metadata call.
3. The Jinja {% for label in labels %} loop emits one SUM(CASE WHEN product = 'X' THEN amount END) AS x expression per label. loop.last controls the trailing comma.
4. The replace(' ', '_') | lower filter normalises label names into snake-case identifiers safe for output column names.
5. The compiled result is plain static SQL — every warehouse can execute it. No dynamic SQL, no EXECUTE IMMEDIATE, no injection risk. The macro handles the dynamic label discovery at compile time and emits static output.

Output (compiled SQL for two labels — Widget, Gadget).

select
  month,
  sum(case when product = 'Widget' then amount end) as widget,
  sum(case when product = 'Gadget' then amount end) as gadget
from sales
group by month
order by month;

Rule of thumb. For any dbt project that needs a dynamic pivot, write a pivot macro once and reuse it. The compiled SQL is static, testable with dbt tests, and portable — you can migrate the same model from Snowflake to BigQuery without changing the macro call. Analytics engineers should reach for compile-time code generation over runtime EXECUTE IMMEDIATE whenever possible.

Senior interview question on dynamic pivot design

A senior interviewer might ask: "You're building a reporting system where the pivot column set can grow monthly. How do you architect the dynamic pivot, and how do you protect downstream consumers from schema drift?"

Solution Using compile-time codegen + explicit label contract

Dynamic pivot architecture — compile-time codegen + schema-drift discipline
===========================================================================

Design decision 1 — where does the pivot happen?
  - dbt macro (compile time)  → BEST, works everywhere
  - Snowflake ANY ORDER BY    → good on Snowflake, still drifts
  - EXECUTE IMMEDIATE         → last resort; hard to test

Design decision 2 — how do downstream consumers handle drift?
  - Contract table: every new label must be added to
    `label_registry(label, first_seen_date, retired_date)`
    with a code review before the pivot picks it up.
  - Materialise the pivot output; downstream reads a stable schema.
  - Emit an event on new-label detection so the BI team is notified.

Design decision 3 — what happens on label removal?
  - Retired labels stay in the output as zero-filled columns until
    the retention window ends, then are dropped.
  - Never silently drop a label — dashboards will break.

Step-by-step trace.

Step	Action	Output
1	Query metadata for distinct labels	Live label list
2	Join against label_registry — keep only registered labels	Approved label list
3	Compile pivot with approved list	Static SQL
4	Materialise pivot output to a stable table	Stable schema
5	Nightly job: detect new labels, emit event to registry team	Drift alert
6	On approval, next compile picks up the new label	Controlled rollout

The architecture turns "silent schema drift" into "explicit review workflow." Downstream dashboards and BI models see a stable schema; new labels go through a change-management step before they hit production.

Output:

Layer	Component	Purpose
Metadata	label_registry table	Contract of approved labels
Compile	dbt pivot macro	Emits static SQL, respects registry
Warehouse	Materialised pivot table	Stable output for BI
Ops	Nightly drift detector	Alerts on unregistered labels

Why this works — concept by concept:

• Compile-time codegen — dbt macros run once per model build, emit static SQL, and are testable with dbt tests. Runtime EXECUTE IMMEDIATE is opaque to the query planner and the test framework.
• Label registry as a contract — a label_registry table converts "new label appeared in source" from a silent event into an explicit workflow. Engineers see it, review it, and approve it before it hits production.
• Materialisation stabilises downstream — writing the pivot output to a table (or dbt incremental model) means BI consumers read a fixed schema. Their dashboards don't break when the pivot picks up a new label mid-day.
• Explicit retirement — retired labels stay as zero-filled columns for a retention window. Deleting a column on the day it stops appearing in source data breaks every downstream that references it.
• Cost — the compile-time metadata query is one round trip per dbt run. The registry table is negligible. The materialisation is one full-scan-plus-write per refresh. Total cost is comparable to a static pivot; the value is in the drift-control workflow, not throughput.

SQL
Topic — aggregation
Dynamic pivot aggregation drills

Practice →

SQL Topic — grouping sets Grouping sets and multi-level reports

Practice →

---

5. Conditional-aggregation fallback

SUM(CASE WHEN …) is the universal portable pivot; ANSI FILTER (WHERE …) is the cleaner form where supported

The mental model in one line: SUM(CASE WHEN col = 'X' THEN val END) AS x is the pivot recipe that works in every SQL dialect ever shipped, and the ANSI FILTER (WHERE …) clause is the cleaner Postgres / SQLite / DuckDB form that plans to the exact same execution — so conditional aggregation is the senior go-to whenever portability, mixed aggregates, or tiny column sets matter more than syntactic sugar. Once you internalise "PIVOT is sugar over SUM(CASE WHEN …)," every dialect gap collapses to a template you fill in.

SUM(CASE WHEN …) — the universal recipe.

• Template: SUM(CASE WHEN pivot_col = 'label' THEN value_col END) AS label. Repeat for each label.
• THEN value_col (no ELSE) makes non-matching rows contribute NULL, which SUM ignores. Do NOT write ELSE 0 — that inflates counts and, more subtly, breaks AVG if you swap the aggregate.
• The outer query needs a GROUP BY on any column NOT in the SUM expression (the row keys). Any column that appears bare in SELECT must be in GROUP BY.
• Works in every SQL dialect ever shipped: Postgres, MySQL, MariaDB, SQL Server, Snowflake, BigQuery, Oracle, DB2, SQLite, DuckDB, ClickHouse. Portability is unmatched.

FILTER (WHERE …) — the ANSI cleaner form.

• Syntax: SUM(value_col) FILTER (WHERE pivot_col = 'label') AS label. The FILTER clause post-filters rows for that specific aggregate.
• ANSI-standard since SQL:2003.
• Supported in: Postgres 9.4+, SQLite 3.30+, DuckDB, Firebird.
• NOT supported in: Snowflake, BigQuery, SQL Server, MySQL, MariaDB, Oracle.
• The query plan is identical to SUM(CASE WHEN …) — this is pure syntactic sugar for readability.

When conditional aggregation wins over PIVOT.

• Mixed aggregates in one row. SUM(CASE WHEN month = 'Jan' THEN amount END) AS jan_sum, COUNT(CASE WHEN month = 'Jan' THEN 1 END) AS jan_count, MAX(CASE WHEN month = 'Jan' THEN amount END) AS jan_max. PIVOT gives one aggregate per output column set; CASE WHEN lets you mix freely.
• Tiny column sets. For 3–5 pivoted columns, the boilerplate saved by PIVOT is small. The debuggability of a plain SELECT + GROUP BY often wins.
• Dialect portability. One query, four warehouses. Only conditional aggregation runs identically everywhere.
• Debuggability. Every runner explains the plan the same way. PIVOT in some warehouses hides the plan behind a syntactic sugar layer that's harder to inspect.
• Predicate integration. Every label branch is a plain SQL expression, so you can add arbitrary conditions: SUM(CASE WHEN month = 'Jan' AND region = 'US' THEN amount END) AS jan_us.

When PIVOT wins over conditional aggregation.

• Many output columns (10+). PIVOT(SUM(amount) FOR month IN ('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')) reads as one line. The equivalent SUM(CASE WHEN …) is 12 lines.
• Code brevity in a Snowflake / BigQuery / SQL Server single-dialect codebase. If you're never going to port the query, use the sugar.
• Dynamic column ergonomics. Snowflake ANY ORDER BY is far cleaner than any hand-coded CASE WHEN generator for unknown label sets.
• Analyst readability. BI teams often read PIVOT more easily than a stack of CASE WHEN expressions.

Performance parity.

• Every warehouse compiles PIVOT down to the same plan as SUM(CASE WHEN …): a GROUP BY scan with per-column expressions in the projection.
• Empirically confirmed on Postgres, Snowflake, BigQuery, SQL Server — same plan, same runtime.
• The one exception: some warehouses may vectorise PIVOT slightly better on wide pivots because they can pre-compute the label bitmap. This is a micro-optimisation; ignore it unless you profile and see a difference.

Common interview probes on conditional aggregation.

• "How do you pivot in a dialect with no PIVOT keyword?" — SUM(CASE WHEN pivot_col = 'label' THEN val END) AS label, repeated per label, with GROUP BY on the row-key columns.
• "What is FILTER (WHERE …) and where does it work?" — the ANSI-standard alternative to CASE WHEN inside an aggregate; supported in Postgres, SQLite, DuckDB.
• "Why not write ELSE 0 in the CASE?" — because non-matching rows should contribute NULL (which SUM ignores). ELSE 0 inflates COUNT and breaks AVG if you swap the aggregate.
• "Same plan as PIVOT?" — yes. PIVOT is syntactic sugar over SUM(CASE WHEN …) under the hood in every modern warehouse.
• "How do you do mixed aggregates (SUM + COUNT)?" — conditional aggregation lets you: SUM(CASE WHEN …) AS s, COUNT(CASE WHEN …) AS c. PIVOT cannot.

Worked example — SUM(CASE WHEN …) monthly pivot

Detailed explanation. The canonical portable pivot: given a long (product, month, sales) table, emit one row per product with columns for each month. Written with SUM(CASE WHEN …), this query runs identically on every dialect.

Question. Write a monthly-sales pivot in SUM(CASE WHEN …) form that produces one row per product with columns for January, February, and March. Explain why ELSE 0 should NOT be used.

Input.

product	month	sales
Widget	Jan	100
Widget	Feb	150
Widget	Mar	200
Gadget	Jan	80
Gadget	Mar	110

Code.

SELECT
  product,
  SUM(CASE WHEN month = 'Jan' THEN sales END) AS jan,
  SUM(CASE WHEN month = 'Feb' THEN sales END) AS feb,
  SUM(CASE WHEN month = 'Mar' THEN sales END) AS mar
FROM sales
GROUP BY product
ORDER BY product;

Step-by-step explanation.

1. CASE WHEN month = 'Jan' THEN sales END returns sales when the row's month is 'Jan' and NULL otherwise. SUM ignores NULLs by definition, so the total is the sum of only the 'Jan' rows.
2. GROUP BY product is required because product appears bare in the projection. Every column not inside an aggregate must be in GROUP BY.
3. The three SUM(CASE WHEN …) expressions each pick a subset of the input and sum them. Same table scan for all three — the planner reads the table once and evaluates the three expressions per row.
4. Non-matching cells come out as NULL (Gadget in February has no row). To display 0 instead, wrap with COALESCE(SUM(CASE WHEN …), 0).
5. Do NOT write ELSE 0 inside the CASE. SUM(CASE WHEN month = 'Jan' THEN sales ELSE 0 END) would work for SUM but breaks two ways: (a) COUNT(CASE WHEN month = 'Jan' THEN sales ELSE 0 END) counts every row (including 0s), inflating the count; (b) AVG(CASE WHEN month = 'Jan' THEN sales ELSE 0 END) averages the zeros in, producing the wrong mean.

Output.

product	jan	feb	mar
Gadget	80	NULL	110
Widget	100	150	200

Rule of thumb. Always write CASE WHEN pred THEN val END (with no ELSE). Wrap the whole aggregate with COALESCE(…, 0) if you need zero-instead-of-null in the output. This one habit prevents 90% of accidental aggregation bugs.

Worked example — mixed aggregates in one row

Detailed explanation. A retail report needs monthly sum, count, max, and avg per product, all in one row. PIVOT cannot express this — it gives you one aggregate per output column set. Conditional aggregation nails it.

Question. Given the same sales table, produce one row per product with columns jan_sum, jan_count, jan_max, feb_sum, feb_count, feb_max. Show that conditional aggregation makes this trivial and PIVOT cannot.

Input.

product	month	sales
Widget	Jan	30
Widget	Jan	70
Widget	Feb	150
Gadget	Jan	80
Gadget	Feb	40
Gadget	Feb	50

Code.

SELECT
  product,

  -- January metrics
  SUM  (CASE WHEN month = 'Jan' THEN sales END) AS jan_sum,
  COUNT(CASE WHEN month = 'Jan' THEN 1     END) AS jan_count,
  MAX  (CASE WHEN month = 'Jan' THEN sales END) AS jan_max,

  -- February metrics
  SUM  (CASE WHEN month = 'Feb' THEN sales END) AS feb_sum,
  COUNT(CASE WHEN month = 'Feb' THEN 1     END) AS feb_count,
  MAX  (CASE WHEN month = 'Feb' THEN sales END) AS feb_max
FROM sales
GROUP BY product
ORDER BY product;

Step-by-step explanation.

1. Every metric per month is its own CASE WHEN inside its own aggregate. SUM sums the sales, COUNT counts the 1s, MAX takes the largest value.
2. COUNT(CASE WHEN month = 'Jan' THEN 1 END) counts rows where month = 'Jan'. The 1 is arbitrary — any non-NULL value works. This is equivalent to COUNT(*) filtered to Jan rows.
3. Widget in Jan has two rows (30, 70) → sum = 100, count = 2, max = 70. The query computes all three in one pass.
4. PIVOT(SUM(sales) FOR month IN ('Jan', 'Feb')) gives only the sums — you can't ask for sum, count, and max in one PIVOT. To get all three, you'd need three separate PIVOT subqueries joined together, or you'd have to concede and use SUM(CASE WHEN …).
5. This is the killer case for conditional aggregation. Every senior data engineer knows: when you need mixed aggregates, PIVOT is out and CASE WHEN is in.

Output.

product	jan_sum	jan_count	jan_max	feb_sum	feb_count	feb_max
Gadget	80	1	80	90	2	50
Widget	100	2	70	150	1	150

Rule of thumb. Any time an interviewer says "and also count / max / avg per month," reach for conditional aggregation. PIVOT is a one-aggregate tool; CASE WHEN is the multi-aggregate tool. Learning to recognise the mixed-aggregate cue in the interview question is a senior signal.

Worked example — FILTER (WHERE …) on Postgres

Detailed explanation. Postgres, SQLite, and DuckDB all support the ANSI FILTER (WHERE …) clause. It reads more like natural language — "SUM of sales filtered to January" — and plans identically to the CASE WHEN form. Postgres teams should default to it.

Question. Rewrite the previous mixed-aggregate query using FILTER (WHERE …). Show the plan is identical to the CASE WHEN version.

Input. Same as the previous example.

Code.

SELECT
  product,

  -- January metrics with FILTER
  SUM  (sales) FILTER (WHERE month = 'Jan') AS jan_sum,
  COUNT(*)     FILTER (WHERE month = 'Jan') AS jan_count,
  MAX  (sales) FILTER (WHERE month = 'Jan') AS jan_max,

  -- February metrics with FILTER
  SUM  (sales) FILTER (WHERE month = 'Feb') AS feb_sum,
  COUNT(*)     FILTER (WHERE month = 'Feb') AS feb_count,
  MAX  (sales) FILTER (WHERE month = 'Feb') AS feb_max
FROM sales
GROUP BY product
ORDER BY product;

Step-by-step explanation.

1. SUM(sales) FILTER (WHERE month = 'Jan') reads: "sum the sales column, but only for rows where month = 'Jan'." The FILTER clause post-filters rows for that specific aggregate.
2. COUNT(*) FILTER (WHERE month = 'Jan') counts rows matching the filter. Cleaner than COUNT(CASE WHEN month = 'Jan' THEN 1 END).
3. The Postgres query planner compiles this to the same executor plan as the CASE WHEN version — you can confirm with EXPLAIN ANALYZE. Both show a single GroupAggregate node with N aggregate expressions.
4. Postgres also supports FILTER on window functions: SUM(sales) FILTER (WHERE month = 'Jan') OVER (PARTITION BY region). This is a Postgres exclusive as of 2026.
5. Snowflake, BigQuery, SQL Server, and MySQL do NOT support FILTER. Trying to use it there produces a syntax error — always confirm your target dialect before adopting FILTER in a codebase.

Output. Identical to the CASE WHEN version — same rows, same columns, same values.

Rule of thumb. On any Postgres, SQLite, or DuckDB project, adopt FILTER (WHERE …) as the house style for conditional aggregates. It's more readable, plans identically, and follows the SQL standard. Just remember it doesn't port to the four biggest cloud warehouses.

Worked example — conditional aggregation for a portable multi-dialect pipeline

Detailed explanation. A data-pipeline library needs a monthly pivot that runs identically on Postgres, Snowflake, BigQuery, and SQL Server. SUM(CASE WHEN …) is the only mechanism that satisfies portability. Show the recipe with COALESCE for missing-cell defaults.

Question. Write a portable monthly pivot for (product, month, sales) that runs on Postgres, Snowflake, BigQuery, and SQL Server without modification. Handle missing cells with COALESCE(0).

Input.

product	month	sales
Widget	Jan	100
Widget	Feb	150
Gadget	Jan	80

Code.

-- Portable across Postgres, Snowflake, BigQuery, SQL Server, MySQL
SELECT
  product,
  COALESCE(SUM(CASE WHEN month = 'Jan' THEN sales END), 0) AS jan,
  COALESCE(SUM(CASE WHEN month = 'Feb' THEN sales END), 0) AS feb,
  COALESCE(SUM(CASE WHEN month = 'Mar' THEN sales END), 0) AS mar
FROM sales
GROUP BY product
ORDER BY product;

Step-by-step explanation.

1. The SUM(CASE WHEN …) core is the portable primitive. Every dialect ships SUM, CASE WHEN, GROUP BY, and ORDER BY — no dialect-specific syntax needed.
2. COALESCE(SUM(…), 0) replaces the NULL cells (product-month combinations with no rows) with 0. COALESCE is ANSI-standard and works on every dialect. (Postgres, MySQL, SQL Server all also support IFNULL / ISNULL — but COALESCE is the portable choice.)
3. The query has no dialect-specific features: no PIVOT, no crosstab, no FILTER, no bracketed identifiers. Runs identically on every warehouse the pipeline touches.
4. To add a fourth month, add a fourth COALESCE(SUM(CASE WHEN …), 0) line. To swap the aggregate to MAX, change all three SUMs to MAX. No structural rework.
5. If the library maintainer needs to adapt this to a dialect that has PIVOT for readability, the swap is mechanical. But the reverse — PIVOT code that needs to become portable — requires a full rewrite.

Output.

product	jan	feb	mar
Gadget	80	0	0
Widget	100	150	0

Rule of thumb. For any pipeline that runs across multiple warehouses, write conditional aggregation as the canonical form. Add native PIVOT variants only when a specific dialect's readability wins a code-review argument. The portable form is the "single source of truth"; native forms are optimisations.

Senior interview question on picking the right shape

A senior interviewer might ask: "You're reviewing a pull request that uses Snowflake PIVOT for a monthly-sales report. When would you push back and ask for SUM(CASE WHEN …) instead, and what would the review comment say?"

Solution Using the "portability + mixed-aggregate + column-count" trilemma

Review checklist — PIVOT vs SUM(CASE WHEN …)

1. Portability
   - Will this query ever run on a non-Snowflake warehouse?
     yes → push back on PIVOT; require SUM(CASE WHEN …)
     no  → PIVOT OK for readability

2. Mixed aggregates
   - Does the report emit SUM + COUNT + MAX + AVG per label?
     yes → PIVOT cannot express this; require SUM(CASE WHEN …)
     no  → PIVOT works

3. Column count
   - Fewer than 6 output columns?
     yes → SUM(CASE WHEN …) is roughly the same length; either OK
     no  → PIVOT wins on brevity

4. Dynamic labels?
   - Unknown label set at compile time?
     yes → Snowflake ANY ORDER BY, or dbt macro emitting CASE WHEN
     no  → static form of either

5. Dialect-specific features (COALESCE, DEFAULT ON NULL)?
   - Need zero-fill on missing cells?
     yes → wrap SUM(CASE WHEN …) with COALESCE; PIVOT needs
           dialect-specific option (Snowflake DEFAULT ON NULL,
           SQL Server outer COALESCE)

Step-by-step trace.

PR under review	Q1 portability	Q2 mixed aggs	Q3 col count	Verdict
Sales report, Snowflake-only, 12-month PIVOT	no	no	12	Approve PIVOT
Sales report, Postgres + Snowflake	yes	no	4	Push back → SUM CASE WHEN
Ops report, SUM + COUNT + MAX per month	no	yes	12	Push back → SUM CASE WHEN
Dashboard, unknown categories	no	no	dynamic	Approve ANY ORDER BY
Small ad-hoc, 3 columns, one-off	no	no	3	Either OK

The review checklist turns "PIVOT or not?" from a taste question into a mechanical decision. Portability and mixed-aggregate concerns force CASE WHEN; column count is a tie-breaker.

Output:

Verdict	Reasoning
Approve PIVOT	Single dialect, single aggregate, many columns
Push back → CASE WHEN	Portability required OR mixed aggregates OR tiny column count
Approve ANY ORDER BY	Snowflake dynamic labels, single aggregate
Push back → dbt macro	Dynamic labels across multiple warehouses

Why this works — concept by concept:

• Trilemma of PIVOT trade-offs — portability, mixed aggregates, and column count are the three axes that decide whether PIVOT fits. Fix any two axes and the third is determined.
• PIVOT is syntactic sugar — it compiles to SUM(CASE WHEN …) in every warehouse. So the choice between them is a readability trade-off, not a performance one. Reviews should focus on maintainability.
• Mixed aggregates force CASE WHEN — PIVOT cannot mix SUM + COUNT + MAX in one output row. Any report that needs those alongside each other must use conditional aggregation. This is the killer question in the review checklist.
• Portability is a hard constraint — one query on multiple warehouses cannot use PIVOT (Postgres has none, MySQL has none). The moment cross-dialect matters, PIVOT is out and SUM(CASE WHEN …) is in.
• Cost — every strategy is O(rows) with one table scan. Column count multiplies the projection cost linearly. Wide pivots (30+ columns) may benefit from PIVOT's vectorisation on some warehouses; this is a micro-optimisation, not a design driver.

SQL
Topic — conditional aggregation
Conditional aggregation problems

Practice →

SQL
Topic — SQL
SQL interview problem library

Practice →

---

Cheat sheet — pivot and unpivot recipes

• Monthly-sales pivot in Snowflake. SELECT * FROM (SELECT product, month, sales FROM t) PIVOT(SUM(sales) FOR month IN ('Jan', 'Feb', 'Mar')). Inline subquery + quoted labels.
• Monthly-sales pivot in BigQuery. Same shape as Snowflake but the subquery form is mandatory. PIVOT(SUM(sales) FOR month IN ('Jan', 'Feb', 'Mar')).
• Monthly-sales pivot in SQL Server. PIVOT(SUM(sales) FOR month IN ([Jan], [Feb], [Mar])) AS p. Bracketed labels + mandatory alias table.
• Monthly-sales pivot in Postgres. SELECT * FROM crosstab($$…$$, $$VALUES ('Jan'), ('Feb'), ('Mar')$$) AS ct(product text, jan int, feb int, mar int). Two-argument form with declared output columns.
• Portable monthly pivot. SELECT product, SUM(CASE WHEN month = 'Jan' THEN sales END) AS jan, SUM(CASE WHEN month = 'Feb' THEN sales END) AS feb FROM t GROUP BY product. Runs on every dialect.
• UNPIVOT in Snowflake / BigQuery. SELECT * FROM t UNPIVOT (sales FOR month IN (jan, feb, mar)). Add INCLUDE NULLS when you need to preserve NULL cells.
• UNPIVOT in SQL Server. SELECT keys, month, sales FROM src UNPIVOT (sales FOR month IN (jan, feb, mar)) AS p. All source columns must share a type — cast in inner subquery if mixed.
• UNPIVOT in Postgres via UNION ALL. SELECT product, 'jan' AS month, jan AS sales FROM t UNION ALL SELECT product, 'feb', feb FROM t …. Verbose but explicit; portable to MySQL too.
• UNPIVOT in Postgres via jsonb_each_text. SELECT keys, key AS name, value FROM t, LATERAL jsonb_each_text(to_jsonb(t) - 'keys'). Zero column-name maintenance; great for wide tables.
• UNPIVOT in Postgres via unnest. SELECT product, d.month, d.sales FROM t, LATERAL unnest(ARRAY['jan','feb','mar'], ARRAY[t.jan, t.feb, t.mar]) AS d(month, sales). Fixed label set, single statement.
• Dynamic pivot in Snowflake. PIVOT(SUM(sales) FOR product IN (ANY ORDER BY product)). Native, declarative, no dynamic SQL.
• Dynamic pivot in SQL Server. Build a STRING_AGG(QUOTENAME(product), ',') label list, concatenate into the pivot SQL, run with sp_executesql @sql. Always use QUOTENAME for injection safety.
• Dynamic pivot in BigQuery. Build the label string with STRING_AGG inside a scripting block, run with EXECUTE IMMEDIATE sql USING @params.
• Dynamic pivot in dbt. Macro that calls run_query() at compile time, loops labels in Jinja, emits static SUM(CASE WHEN col = 'X' THEN val END) AS x. Portable output, testable, no runtime dynamic SQL.
• Conditional aggregation template. SUM(CASE WHEN pivot_col = 'label' THEN val END) AS label. No ELSE 0 — non-matching rows should contribute NULL. Wrap with COALESCE(…, 0) for zero-fill.
• FILTER (WHERE …) form. SUM(sales) FILTER (WHERE month = 'Jan') AS jan. Postgres, SQLite, DuckDB only. Plans identically to CASE WHEN.
• Missing-cell defaults. Snowflake: PIVOT(…) DEFAULT ON NULL (0). SQL Server: COALESCE([Jan], 0) in outer projection. BigQuery: COALESCE(Jan, 0) in outer projection. Portable: COALESCE(SUM(CASE WHEN …), 0).
• When to use PIVOT. 6+ output columns, single dialect, single aggregate, static labels. When to use CASE WHEN: portability, mixed aggregates, tiny column sets, debuggability.
• MySQL emulation. No PIVOT, no UNPIVOT. Use SUM(CASE WHEN …) for pivot, UNION ALL or JSON_TABLE (8.0.4+) for unpivot. Stored procedures with dynamic SQL for compile-time-unknown labels.

Frequently asked questions

Does Postgres support PIVOT?

Not with the PIVOT keyword — Postgres has no native PIVOT clause. Postgres teams have three options: (1) the tablefunc extension's crosstab(query, categories) function, which is the classical answer but requires declaring output column names and types explicitly in AS ct(…); (2) SUM(CASE WHEN month = 'Jan' THEN sales END) AS jan, the universal portable recipe that works on every dialect; (3) the ANSI FILTER (WHERE …) clause supported by Postgres 9.4+, which reads slightly more naturally than CASE WHEN and plans identically. For any Postgres query you might port to another warehouse, use conditional aggregation. For Postgres-only reports where the column count is high, crosstab remains readable.

What is the difference between PIVOT and crosstab?

PIVOT is a SQL clause in a FROM context that shapes a subquery — it treats every implicit column as a grouping key and expands one column into many. crosstab is a Postgres tablefunc function that takes two SQL strings: one for the long-form input (must be ORDER BY row_key, category) and one for the category order pin list. crosstab requires you to declare the output column list and types in an AS ct(row_key type, cat_a type, …) alias; PIVOT infers them from the label list. Functionally the two produce identical output for the same input; syntactically crosstab is more verbose and has stricter ordering requirements. If you're on Snowflake, BigQuery, or SQL Server, use PIVOT; if you're on Postgres with tablefunc installed, crosstab works but many teams still prefer SUM(CASE WHEN …) for portability.

Does BigQuery PIVOT work with dynamic column lists?

Not directly — bigquery pivot requires a static IN list in the pivot clause. To handle unknown labels at query time, wrap the pivot in a BigQuery scripting block: declare a STRING variable, populate it by running STRING_AGG(DISTINCT CONCAT("'", pivot_col, "'") ORDER BY pivot_col) against the source, then EXECUTE IMMEDIATE FORMAT("SELECT * FROM t PIVOT(SUM(val) FOR col IN (%s))", cols). Always use USING @param binding when the label list depends on user input to avoid SQL injection. The compile-time alternative — a dbt macro that discovers labels with run_query() and emits static SUM(CASE WHEN …) — is preferred for analytics workflows because the resulting SQL is testable, cacheable, and dialect-portable.

How do I UNPIVOT in MySQL?

MySQL has no UNPIVOT keyword. The two idiomatic answers are UNION ALL (works on every MySQL version) and JSON_TABLE (MySQL 8.0.4+). For UNION ALL, write one SELECT per unpivoted column: SELECT product, 'jan' AS month, jan AS sales FROM t UNION ALL SELECT product, 'feb', feb FROM t …. For JSON_TABLE, build a JSON array of label-value pairs and join against it: SELECT t.product, j.month, j.sales FROM t, JSON_TABLE(JSON_ARRAY(JSON_OBJECT('month', 'jan', 'sales', t.jan), JSON_OBJECT('month', 'feb', 'sales', t.feb)), '$[*]' COLUMNS (month VARCHAR(3) PATH '$.month', sales INT PATH '$.sales')) AS j. UNION ALL reads more clearly for 3–5 columns; JSON_TABLE scales better beyond that. For any wider unpivot, generate the SQL in application code — the boilerplate savings pay for the templating complexity.

When should I use conditional aggregation instead of PIVOT?

Three cases push you to SUM(CASE WHEN …) over PIVOT. First, portability — one query that runs identically on Postgres AND Snowflake AND BigQuery AND SQL Server AND MySQL cannot use PIVOT because Postgres and MySQL have no such keyword. Second, mixed aggregates in a single output row — SUM + COUNT + MAX + AVG per label combination is not expressible with PIVOT (which allows only one aggregate) but is a one-line pattern with conditional aggregation. Third, tiny column sets (3–5 outputs) where PIVOT's boilerplate savings are small and the debuggability of a plain SELECT + GROUP BY wins. Reach for PIVOT when the output has 6+ columns and readability is the main win, or when you're using Snowflake's flagship ANY ORDER BY dynamic form. Every dialect compiles PIVOT to the same plan as SUM(CASE WHEN …), so performance is never the deciding factor.

What is the FILTER (WHERE …) clause and where does it work?

FILTER (WHERE …) is the ANSI SQL:2003 clause that post-filters rows for a specific aggregate. Syntax: SUM(sales) FILTER (WHERE month = 'Jan') AS jan. It reads more naturally than SUM(CASE WHEN month = 'Jan' THEN sales END) and plans identically — the query optimiser produces the exact same execution plan for both forms. Supported dialects in 2026: Postgres 9.4+, SQLite 3.30+, DuckDB, and Firebird. NOT supported: Snowflake, BigQuery, SQL Server, MySQL, MariaDB, Oracle. On Postgres, FILTER also works with window functions: SUM(sales) FILTER (WHERE month = 'Jan') OVER (PARTITION BY region) — a niche superpower. For any Postgres-only or SQLite-only codebase, adopt FILTER as the house style; for a portable library, fall back to SUM(CASE WHEN …) because it works everywhere.

Practice on PipeCode

• Drill the unpivoting practice library → for the wide-to-long reshape family — UNPIVOT, UNION ALL, and the Postgres jsonb one-liner.
• Rehearse on conditional-aggregation problems → when the interviewer wants a portable SUM(CASE WHEN …) pivot.
• Sharpen grouping-sets drills → for ROLLUP, CUBE, and multi-level aggregation reports adjacent to pivot patterns.
• Stack the aggregation library → for SUM / COUNT / MAX / MIN / AVG interview probes that underpin every pivot.
• Layer the CASE expression library → for the conditional-aggregation primitive that powers portable pivots.
• Complement the reshape grind with the CASE WHEN topic collection → for shorter warm-up drills.
• For general SQL sharpening, work through the SQL problem library →.
• For the broader data-engineering surface, read top data engineering interview questions →.
• Stack the prerequisites with the only 5 skills you need to become a data engineer →.
• For the broader SQL interview surface, take the SQL for Data Engineering course →.

Pipecode.ai is Leetcode for Data Engineering — every PIVOT, UNPIVOT, and conditional-aggregation recipe above ships with hands-on practice rooms where you write the Snowflake `PIVOT`, wire the Postgres `crosstab`, unpivot with `UNION ALL`, and pick the portable `SUM(CASE WHEN …)` against real graded inputs. PipeCode pairs every reading with 450+ DE-focused problems and a real-time scoring engine, so you never have to wonder whether your `sql pivot` answer holds up under a senior interviewer's depth probes.

Practice pivot patterns now →
Conditional aggregation drills →