SQL Pivot Explained: Turn Rows Into Columns With CASE, FILTER and PIVOT
A SQL pivot turns values stored in rows, such as quarters or regions, into separate columns for a report. Learn the portable CASE pattern, the FILTER shortcut, dynamic columns and unpivoting, with every query run and every trap shown.
- A SQL pivot is grouping plus one aggregate per output column:
SUM(CASE WHEN quarter='Q1' THEN amount END). - The CASE pattern works in every major database;
FILTER (WHERE ...)is a shorter standard form in PostgreSQL and SQLite. - Empty cells come back as NULL. Use
COALESCE(..., 0)when a report needs zeros. - SQL needs column names up front, so pivots with unknown values need dynamic SQL built safely in code.
Your sales table stores one row per region, quarter and product, which is perfect for storage and terrible for a manager who wants a grid: regions down the side, quarters across the top. Turning those row values into columns is called a SQL pivot. Some databases have a PIVOT keyword, but the technique that works everywhere is conditional aggregation with CASE.
We ran every query below in SQLite 3.50.4 through Python 3.13, and the results are copied from that run. The native PIVOT syntax for SQL Server and Oracle is shown for reference and was not run. If CASE is new to you, read our SQL CASE WHEN guide first.
What is a pivot in SQL?
A pivot rotates data. Distinct values in one column, such as Q1 to Q4, become column headers, and an aggregate, such as a sum, fills each cell. It is the same idea as a pivot table in a spreadsheet, done in the query instead. The opposite move, turning columns back into rows, is called an unpivot.
The sample data
CREATE TABLE sales(region TEXT, quarter TEXT, product TEXT, amount INT);
INSERT INTO sales VALUES
('North','Q1','Pen',100),('North','Q2','Pen',150),('North','Q1','Ink',40),('North','Q3','Ink',60),
('South','Q1','Pen',90),('South','Q2','Ink',30),('South','Q2','Pen',70),('West','Q4','Pen',200);
Eight rows in “long” format. Notice that some region and quarter pairs have no sales at all, which matters later.
The portable pivot: SUM with CASE
SELECT region,
SUM(CASE WHEN quarter='Q1' THEN amount END) AS q1,
SUM(CASE WHEN quarter='Q2' THEN amount END) AS q2,
SUM(CASE WHEN quarter='Q3' THEN amount END) AS q3,
SUM(CASE WHEN quarter='Q4' THEN amount END) AS q4,
SUM(amount) AS total
FROM sales
GROUP BY region
ORDER BY region;
| region | q1 | q2 | q3 | q4 | total |
|---|---|---|---|---|---|
| North | 140 | 150 | 60 | NULL | 350 |
| South | 90 | 100 | NULL | NULL | 190 |
| West | NULL | NULL | NULL | 200 | 200 |
Here is how it works. GROUP BY region gives one output row per region. Inside each group, the CASE returns the amount only for rows in the matching quarter and NULL for the rest. SUM ignores NULL, so each column adds up just its quarter. North’s Q1 is 140 because it had two Q1 rows, Pen 100 and Ink 40. Our GROUP BY and HAVING guide covers grouping itself in more depth.
NULL or zero in empty cells?
The empty cells above are NULL, not zero, because no row matched and SUM of nothing is NULL. Both fixes below gave identical results for Q3:
SUM(CASE WHEN quarter='Q3' THEN amount ELSE 0 END)
COALESCE(SUM(CASE WHEN quarter='Q3' THEN amount END), 0)
-- both: North 60, South 0, West 0
Which one is right depends on meaning. Zero says “we sold nothing”. NULL can honestly say “no data”, which is different if a region did not exist yet. Decide with the people reading the report. Our SQL NULL values guide explains why NULL behaves this way in sums, counts and comparisons.
The FILTER clause shortcut
The SQL standard has a cleaner form, supported by PostgreSQL and SQLite, though not by MySQL or SQL Server:
SELECT region,
SUM(amount) FILTER (WHERE product='Pen') AS pen,
SUM(amount) FILTER (WHERE product='Ink') AS ink
FROM sales GROUP BY region ORDER BY region;
-- North 250 100 | South 160 30 | West 200 NULL
It does exactly what the CASE version does but reads more like English. If your code must run on several databases, stick with CASE.
Pivoting counts instead of sums
SELECT product,
COUNT(CASE WHEN region='North' THEN 1 END) AS north,
COUNT(CASE WHEN region='South' THEN 1 END) AS south
FROM sales GROUP BY product ORDER BY product;
-- Ink 2 1 | Pen 2 2
COUNT(expression) counts only non-NULL values, so the missing ELSE is what makes this work. If you add ELSE 0, every row counts, because zero is not NULL, and the pivot silently returns group sizes instead. That is a classic bug worth a test.
What happens if you forget GROUP BY
SELECT SUM(CASE WHEN quarter='Q1' THEN amount END) FROM sales;
-- 230
Without GROUP BY, the whole table is one group, so we got a single number: total Q1 sales across all regions. The query runs without error, which is why this mistake can survive into a dashboard. Always check the row count of a pivot: here it should equal the number of regions.
Totals rows and percentage columns
Reports often need a grand total row and shares of the total. A UNION ALL adds the totals row, and a scalar subquery gives the denominator:
SELECT region, SUM(amount) FROM sales GROUP BY region
UNION ALL
SELECT 'ALL', SUM(amount) FROM sales;
-- last row: ('ALL', 740)
SELECT region, ROUND(100.0 * SUM(amount) / (SELECT SUM(amount) FROM sales), 1)
FROM sales GROUP BY region ORDER BY region;
-- North 47.3 | South 25.7 | West 27.0
Note 100.0 rather than 100: with two integers, many databases do integer division and every share becomes 0. Our UNION vs UNION ALL guide explains why UNION ALL is the right choice for a totals row. For running totals or ranks across the pivoted rows, use SQL window functions.
Tip: check that the totals column equals the sum of the pivoted columns. If a quarter value like 'q1' (lowercase) exists, it falls into no column but still counts in the total, and the mismatch exposes it.
Dynamic pivot columns
SQL needs to know the output columns before it runs. If a new quarter or product appears, a hard-coded pivot ignores it. The usual fix is to read the distinct values first and build the query in application code. We did it in Python, passing the values as parameters rather than pasting them into the SQL:
cols = [r[0] for r in conn.execute("SELECT DISTINCT quarter FROM sales ORDER BY quarter")]
sql = "SELECT product, " + ", ".join(
f'SUM(CASE WHEN quarter=? THEN amount END) AS "{c}"' for c in cols
) + " FROM sales GROUP BY product ORDER BY product"
rows = conn.execute(sql, cols).fetchall()
# columns: product, Q1, Q2, Q3, Q4
# Ink 40 30 60 None | Pen 190 220 None 200
The comparison values are safe as parameters. Column aliases cannot be parameters, so they come straight from the data here; in production, check them against an allow-list or a strict pattern first, or a strange value could break the query. SQL Server users often build the same thing with STRING_AGG and sp_executesql.
Unpivot: columns back to rows
We saved the pivot as a table called wide, then rebuilt long rows with UNION ALL, one branch per column:
SELECT region, 'Q1' AS quarter, q1 AS amount FROM wide WHERE q1 IS NOT NULL
UNION ALL SELECT region, 'Q2', q2 FROM wide WHERE q2 IS NOT NULL
UNION ALL SELECT region, 'Q3', q3 FROM wide WHERE q3 IS NOT NULL
UNION ALL SELECT region, 'Q4', q4 FROM wide WHERE q4 IS NOT NULL;
-- 6 rows, e.g. ('North','Q1',140), ('North','Q2',150), ('North','Q3',60)
We got 6 rows back, not the original 8, because the pivot had already summed North’s two Q1 rows and South’s two Q2 rows. Pivoting with an aggregate loses detail; keep the original long table as the source of truth. Unpivoting is common when you import a spreadsheet with a column per month and want it in a proper table.
Native PIVOT and UNPIVOT syntax
SQL Server and Oracle have dedicated keywords. This SQL Server form is shown for reference only; we did not run it:
-- SQL Server (not run)
SELECT region, [Q1], [Q2], [Q3], [Q4]
FROM (SELECT region, quarter, amount FROM sales) AS src
PIVOT (SUM(amount) FOR quarter IN ([Q1], [Q2], [Q3], [Q4])) AS p;
It is shorter for one aggregate but still needs the column list written out, and it does not port to PostgreSQL, MySQL or SQLite. Many teams therefore standardise on the CASE pattern and wrap it in a view or a CTE so reports stay readable.
How to test a pivot query
- Check the row count equals the number of groups you expect.
- Check each row total equals the sum of its pivoted cells.
- Check the grand total equals
SUM(amount)on the raw table. - Add data with an unexpected value, such as a new quarter, and confirm the report either shows it or fails loudly.
- Decide and test whether empty cells should be
NULLor 0.
Final thoughts
A SQL pivot is just grouping with one conditional aggregate per column. Learn the SUM(CASE ...) pattern once and it works on every database you will meet. Then add the habits that keep reports honest: decide between NULL and zero, never forget GROUP BY, reconcile totals, and build dynamic columns safely.
Frequently asked questions
How do I pivot rows into columns in SQL?
Group by the row label and write one aggregate per output column, such as SUM(CASE WHEN quarter = ‘Q1’ THEN amount END). This conditional aggregation works in every major database.
Does MySQL have a PIVOT function?
No. MySQL has no PIVOT keyword, so use SUM or COUNT with CASE and GROUP BY. SQL Server and Oracle have native PIVOT syntax, and PostgreSQL and SQLite support the FILTER clause.
Why does my pivot show NULL instead of 0?
When no rows match a column, SUM returns NULL. Wrap it in COALESCE with 0, or add ELSE 0 to the CASE, if the report should show zero.
How do I pivot when the column values are not known in advance?
Query the distinct values first, then build the SQL in code. Pass compared values as parameters and validate column names against an allow-list, because aliases cannot be parameters.
What is unpivot in SQL?
Unpivot turns columns back into rows. Use UNION ALL with one SELECT per column, or the UNPIVOT keyword where supported. It cannot restore detail that an aggregate already summed.
Keep learning
Need help with testing, WordPress or SEO?
We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.