How do you pivot rows into columns?

Q: How do you pivot rows into columns?

**Target Audience:** Anyone building reports in SQL, from beginners who know `GROUP BY` to candidates who will be asked about dynamic pivots and engine-specific `PIVOT` syntax. **Difficulty:** Hard **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15 with the `tablefunc` 1.0 extension, both run for this page. SQL Server and Oracle syntax is cited, not run. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result shown was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First A shop keeps its till receipts in a shoebox: every slip says which branch, which quarter, how much. That is a perfect way to **record** sales. But the owner wants a single sheet of paper with a row per branch and four boxes across, Q1 to Q4, e

The problem

Question presented to candidate: "Our sales table has one row per sale with a region, a quarter and an amount. Finance wants a report with one row per region and one column per quarter, Q1 to Q4, holding the total. Write that query. Then tell me what you would do if the list of quarters is not known in advance, and how the answer differs between PostgreSQL, SQL Server and SQLite."

What a strong answer should cover:

Pivoting turns the distinct values of one column into output columns; the portable way is conditional aggregation: GROUP BY the row key and one SUM(CASE WHEN quarter = 'Q1' THEN amount END) per column
Empty cells: without ELSE the cell is NULL, with ELSE 0 or COALESCE it is 0; choose deliberately, and make COUNT versus SUM behave right
The FILTER (WHERE ...) clause (PostgreSQL, SQLite 3.30+) as a cleaner spelling of the same thing
Engine features: SQL Server and Oracle PIVOT, PostgreSQL crosstab() from the tablefunc extension; all need the column list written out
SQL needs the output columns at parse time, so a pivot over unknown values needs dynamic SQL generated from the data (or pivoting in the application); and the reverse, unpivot, with UNION ALL or UNPIVOT

Clarifying questions expected:

Is the set of pivot values fixed (four quarters) or does it grow with the data?
Should a missing combination show 0 or be empty?
Is the result for a human report (pivot in SQL or the BI tool) or for code (keep it long)?

Code / implementation expected: Yes — a conditional-aggregation pivot query; optionally the dynamic-SQL generator.

Schema used by the examples:

pivot

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Anyone building reports in SQL, from beginners who know GROUP BY to candidates who will be asked about dynamic pivots and engine-specific PIVOT syntax. Difficulty: Hard Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15 with the tablefunc 1.0 extension, both run for this page. SQL Server and Oracle syntax is cited, not run.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result shown was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

A shop keeps its till receipts in a shoebox: every slip says which branch, which quarter, how much. That is a perfect way to record sales.

The solution is hidden so you can try first

14 min read · 13 sections

Run the code

Edit the query and run it against the tables for this question.

SQLConditional aggregation: one SUM(CASE ...) per quarter, NULL vs ELSE 0, plus a total

SQLThe FILTER clause: counts and half-year sums per region (SQLite 3.30+ and PostgreSQL)

PostgreSQL crosstab() from the tablefunc extension

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
  $$SELECT region, quarter, SUM(amount)::int
    FROM sales GROUP BY region, quarter ORDER BY 1, 2$$,
  $$VALUES ('Q1'), ('Q2'), ('Q3'), ('Q4')$$
) AS ct(region text, q1 int, q2 int, q3 int, q4 int)
ORDER BY region;