1. Home
  2. Blog
  3. Databases & SQL

Databases & SQL

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.

9 min read
SQL pivot explained: turn rows into columns with CASE, FILTER and PIVOT

Key takeaways

  • 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 NULL or 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

Work with Stack Quora

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.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

BM25 Explained: How Keyword Search Ranks Results, With Python Code and Hybrid Search

BM25 explained with a pure Python implementation we ran: the formula, IDF, term saturation, the k1 and b parameters, why…

Read article →
Software Development

HTTP Methods Explained: GET, POST, PUT, PATCH, DELETE, Tested on a Live Server

HTTP methods explained with a small server we ran: GET, POST, PUT, PATCH, DELETE, HEAD and OPTIONS, safe vs idempotent,…

Read article →
DevOps

Git Bisect Explained: Find the Commit That Broke Your Code, Manually or Automatically

Git bisect explained with a tested repository: find the one bad commit among 41 in 5 test runs, automate it…

Read article →
Test Automation

pytest Markers Explained: Custom Marks, skip, skipif, xfail and -m Filters

pytest markers explained with a tested suite: register custom marks, run only smoke tests with -m, combine filters with and,…

Read article →
Software Testing

Positive vs Negative Testing: Differences, 24 Real Test Cases and a Checklist

Positive vs negative testing explained with a real signup form: 6 positive and 18 negative test cases run in pytest,…

Read article →
Databases & SQL

SQL ORDER BY Multiple Columns: ASC, DESC, NULLS LAST, Case and Custom Order

SQL ORDER BY with multiple columns explained with tested queries: mixed ASC and DESC, tie-breakers, NULLS FIRST and LAST, case-insensitive…

Read article →
Databases & SQL

SQL Running Total: SUM OVER, Per-Group Totals, Moving Sums and the RANGE Trap

How to calculate a SQL running total with SUM() OVER: per-group totals, daily cumulative sums, moving sums, percent of total,…

Read article →
Software Development

JavaScript Sort Array: Numbers, Objects, Strings and Why 10 Comes Before 5

How to sort an array in JavaScript correctly: numbers with a compare function, objects by one or more fields, strings…

Read article →
Software Development

Python String Methods: Cheat Sheet, Real Examples and the strip() Trap

Python string methods with tested examples: split, join, strip, replace, find, startswith, is-methods, casefold, partition and translate, plus the strip()…

Read article →
Software Development

Python zip() Explained: Loop Two Lists at Once, Unzip, strict=True and Traps

Python zip() pairs items from several lists in one loop. Tested examples for dicts, unzipping with *, strict=True, zip_longest, sliding…

Read article →
Artificial Intelligence

Cosine Similarity Explained: Formula, Worked Example and Python Code

Cosine similarity explained simply: the formula, a worked example by hand, pure Python and NumPy code, cosine vs Euclidean distance…

Read article →
Software Development

UTF-8 Encoding Explained: How Text Becomes Bytes and How to Fix Garbled Text

UTF-8 encoding explained with byte-level examples we ran in Python: how characters become 1 to 4 bytes, why café turns…

Read article →
DevOps

Git Squash Commits: 4 Easy Ways, Tested Step by Step (rebase -i, reset, merge)

Git squash commits explained with 4 tested methods: interactive rebase, reset –soft, merge –squash and fixup with autosquash. Before-and-after logs,…

Read article →
Test Automation

pytest parametrize Explained: 7 Patterns with Tested Examples

pytest parametrize explained with 7 tested patterns: basic cases, readable ids, stacked decorators, pytest.param with xfail and skip, indirect fixtures…

Read article →
Software Testing

Retesting vs Regression Testing: 8 Key Differences with a Real Bug Fix Example

Retesting vs regression testing explained with a real bug fix run in pytest: retesting confirmed the fix, the regression run…

Read article →
Databases & SQL

Delete Duplicate Rows in SQL: 5 Safe Methods, Tested Step by Step

How to delete duplicate rows in SQL safely: find them, preview them, then remove them with ROW_NUMBER, MIN(id), EXISTS, rowid…

Read article →
Databases & SQL

SQL RANK vs DENSE_RANK vs ROW_NUMBER: Differences with Tested Examples

SQL RANK vs DENSE_RANK vs ROW_NUMBER in one table: how each handles ties, top-N per group, the second-highest salary trap…

Read article →
Software Development

JavaScript Optional Chaining (?.) Explained: 8 Examples, ?? Defaults and Traps

JavaScript optional chaining stops “Cannot read properties of undefined” errors. 8 tested examples for properties, arrays and methods, ?? vs…

Read article →
Artificial Intelligence

RAG Chunking Strategies Explained: Fixed, Overlap, Sentence, Recursive and Headings, Measured

RAG chunking strategies compared on one document with tiktoken 0.14: fixed-size, overlap, sentence packing, recursive splitting and heading-based chunks, measuring…

Read article →
DevOps

Git Stash Explained: Save, Apply, Pop and Untracked Files, Tested

Git stash explained with commands we ran on Git 2.56: push with a message, untracked files, list and show, apply…

Read article →
Software Testing

Pairwise Testing Explained: How All-Pairs Cuts 216 Tests to 13, Tested

Pairwise testing explained with a generator we ran in Python 3.13: a checkout with 216 combinations covered by 13 tests,…

Read article →
Test Automation

Test Doubles Explained: Dummy, Stub, Spy, Mock and Fake, with Tested Python Code

Test doubles explained with one Python example run in pytest 9.2: dummy, stub, spy, mock and fake, when to use…

Read article →
Databases & SQL

SQL COALESCE Explained: Defaults, NULLIF, IFNULL vs ISNULL and Traps, Tested

SQL COALESCE explained with queries we ran in SQLite 3.50: first non-null values, empty strings, NULL in maths, NULLIF for…

Read article →
Databases & SQL

SQL Self Join Explained: Employee-Manager, Pairs and Row Comparisons, Tested

SQL self join explained with queries we ran in SQLite 3.50: employee and manager, earning more than the manager, unique…

Read article →
Artificial Intelligence

LLM Context Window Explained: Tokens, Limits and How to Fit More, Measured

The LLM context window explained with token counts we measured using tiktoken: words vs tokens for prose, code and Hindi,…

Read article →
DevOps

Git Cherry-Pick Explained: Copy the Commit You Need, Safely, Tested

Git cherry-pick explained with commands we ran in Git 2.56: copying a hotfix to a release branch, new hashes and…

Read article →
Test Automation

Snapshot Testing Explained: How It Works, When It Helps and When It Hurts

Snapshot testing explained with a pytest and syrupy demo we ran: creating snapshots, reading the diff after a code change,…

Read article →
Software Testing

Mutation Testing Explained: Why 100% Coverage Still Missed Our Bug

Mutation testing explained with a small mutation tool we built and ran: 14 mutants, a test suite with 100% line…

Read article →
Artificial Intelligence

Prompt Injection Explained: How AI Apps Get Tricked and How to Defend Them

Prompt injection explained for builders: direct and indirect attacks, why keyword filters fail (our test blocked 1 of 4), and…

Read article →
DevOps

DNS Records Explained: A, AAAA, CNAME, MX, TXT and TTL With Real Lookups

DNS records explained with lookups we ran: how resolution works, A and AAAA, CNAME rules, MX and null MX, TXT…

Read article →
DevOps

Feature Flags Explained: Safe Releases, Gradual Rollouts and Flag Hygiene

Feature flags explained with a rollout simulation we ran: flag types, percentage rollouts with stable hashing, kill switches, allow lists,…

Read article →
Test Automation

Test Data Generation With Python Faker: Realistic, Repeatable and Safe Data

Test data generation with Python Faker, run and measured: seeding for repeatable data, locales, unique values, domain-shaped records, edge cases…

Read article →
Software Testing

Shift-Left Testing Explained: Find Bugs Earlier Without Slowing the Team

Shift-left testing explained: what it means, why early defects are cheaper, practices from requirement reviews to CI checks, the tester's…

Read article →
Artificial Intelligence

LLM Structured Output Explained: Getting Reliable JSON From AI Models

LLM structured output explained with validation we ran in Pydantic: JSON mode and schemas, constrained decoding, the ways model output…

Read article →
Test Automation

API Contract Testing Explained: Catch Breaking Changes Before They Ship

API contract testing explained with a schema check we ran: what a contract is, consumer-driven contracts, Pact, OpenAPI, which changes…

Read article →
Software Testing

Risk-Based Testing Explained: Score Risks, Build a Matrix and Test What Matters

Risk-based testing explained step by step: identifying risks, scoring likelihood and impact, building a risk matrix, mapping risk to test…

Read article →