How Do You Pivot Data in SQL?

You use conditional aggregation: for each column you want in the output, you add one aggregate expression that sums or counts only the rows belonging to that column. Each expression is a CASE that returns the value for its own bucket and something neutral for everything else.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  order_date TEXT NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders (id, customer_id, order_date, amount) VALUES
  (1, 1, '2026-01-08', 120.00),
  (2, 1, '2026-02-05', 200.00),
  (3, 1, '2026-03-11',  60.00),
  (4, 2, '2026-01-14', 300.00),
  (5, 2, '2026-02-19', 150.00),
  (6, 2, '2026-03-02', 220.00);

SELECT customer_id,
       ROUND(SUM(CASE WHEN strftime('%Y-%m', order_date) = '2026-01' THEN amount ELSE 0 END), 2) AS jan,
       ROUND(SUM(CASE WHEN strftime('%Y-%m', order_date) = '2026-02' THEN amount ELSE 0 END), 2) AS feb,
       ROUND(SUM(CASE WHEN strftime('%Y-%m', order_date) = '2026-03' THEN amount ELSE 0 END), 2) AS mar
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

Two rows come back. Customer 1 shows 120.00 in January, 200.00 in February and 60.00 in March. Customer 2 shows 300.00, 150.00 and 220.00. Six rows of data have become two rows with three measure columns.

Three details make it work. The CASE has an ELSE branch — using 0 for a sum or omitting the row for a count — because a CASE without ELSE returns NULL, and SUM ignores NULLs, so a missing bucket would quietly contribute nothing while still leaving a NULL in the output for a MIN or MAX. The neutral value differs by aggregate: SUM wants 0, COUNT wants to count only the matching rows, and MIN or MAX wants the NULL to be ignored. And the grouping key stays outside the CASE, so each row of the output is one customer.

The dialect note: PostgreSQL has a native PIVOT statement, and SQL Server has PIVOT, both of which take a column-to-column mapping. Neither MySQL nor SQLite has one, so conditional aggregation is the portable approach that works everywhere.

The same technique, this time counting rows rather than summing money. When you count with a CASE, the neutral branch should be NULL rather than 0, so that COUNT ignores it instead of counting a phantom row.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status VARCHAR(20) NOT NULL
);

INSERT INTO customers (id, name) VALUES
  (1, 'Ada Lovelace'),
  (2, 'Grace Hopper'),
  (3, 'Alan Turing');

INSERT INTO orders (id, customer_id, status) VALUES
  (1, 1, 'shipped'),
  (2, 1, 'shipped'),
  (3, 1, 'pending'),
  (4, 2, 'pending'),
  (5, 3, 'cancelled'),
  (6, 3, 'cancelled');

SELECT c.name AS customer,
       COUNT(CASE WHEN o.status = 'shipped'   THEN 1 END) AS shipped,
       COUNT(CASE WHEN o.status = 'pending'   THEN 1 END) AS pending,
       COUNT(CASE WHEN o.status = 'cancelled' THEN 1 END) AS cancelled
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.name;

Three rows come back. Ada Lovelace has 2 shipped and 1 pending and 0 cancelled. Grace Hopper has 0 shipped, 1 pending and 0 cancelled. Alan Turing has 0 shipped, 0 pending and 2 cancelled.

The interesting detail is Grace Hopper's row. Because the ELSE branch of the CASE is absent, the non-matching rows produce NULL, and COUNT ignores NULLs — so her shipped count is 0 rather than 1. If you had written COUNT(CASE WHEN ... THEN 1 ELSE 0 END), every bucket would be at least 1 and the numbers would be wrong.

That same NULL-versus-zero distinction decides whether a pivot shows a blank cell or a zero, and it is worth deciding deliberately rather than by accident. A report where "0 shipped" means "we checked and there were none" should use the COUNT(CASE) form. A report where "no data" should be distinguishable from zero needs the NULL preserved, which means either not using an aggregate at all or using SUM over a CASE with no ELSE, and then coalescing at the presentation layer.

One more note on the join: this uses an inner join, so a customer with no orders at all would not appear. Switch to a LEFT JOIN if the report should show every customer with zeroes across the board, and count the joined id rather than a CASE, since the outer-joined rows will be NULL anyway.

Conditional aggregation needs one expression per output column, and each expression has to name its bucket literally. That means the number of columns and their names are baked into the SQL text. As soon as the set of months, statuses or products is decided by the data rather than by the developer, the static query is the wrong tool.

The second limit is that the pivoted columns are hard-coded literals, so a new status appearing tomorrow shows up as zeroes across the existing columns rather than as its own column. And with many buckets the query becomes unreadable — twenty monthly columns means twenty near-identical CASE expressions, each of which is another string the optimizer has to parse and plan.

There are three ways forward, in increasing order of complexity. First, generate the SQL dynamically: query the distinct bucket values, build the pivot statement in application code, and execute it. This is simple, keeps the whole thing in one query, and is what many reporting tools do. The catch is that it needs a write-capable connection string, which some production setups forbid, and the generated statement has to be parameterised properly so the bucket values cannot be injected.

Second, keep the data long and pivot in the presentation layer. Most BI tools and reporting libraries can pivot a tidy dataset themselves, and shipping customer, month and amount as three columns is smaller, cacheable and easier to filter. This is usually the right answer for a dashboard.

Third, and for very large or very dynamic data, move the pivot out of the query entirely: a columnar warehouse such as BigQuery or Snowflake handles dynamic pivots natively, and PostgreSQL's crosstab function in the tablefunc extension does it server-side with a dynamic column list.

For an interview, the strongest answer is usually the middle one: keep the data long, pivot downstream, and only generate dynamic SQL when you genuinely must produce a wide result set from the database itself.