Pivoting reshapes category values into separate output columns, often using conditional aggregation for a fixed set of categories.
A customer report may need one row per customer with paid and pending counts as separate columns. The source stores those statuses as row values. Conditional aggregation reshapes them while keeping the grouping and missing-category policy explicit.
This reference adds detail to the main course. You can read it independently: the small database below supplies every table and row used in the examples. Start with the question each query answers, then compare the returned values with the data rather than memorising a line of syntax.
Set up the practice database
Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.
Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(40) NOT NULL,
city VARCHAR(40),
manager_id INTEGER
);
CREATE TABLE sales (
id INTEGER PRIMARY KEY,
customer_id INTEGER,
amount DECIMAL(10,2),
status VARCHAR(20) NOT NULL,
created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
(1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
(3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
(101,1,10,'paid','2026-01-01'),
(102,1,30,'pending','2026-01-02'),
(103,2,20,'paid','2026-01-02'),
(104,3,NULL,'pending','2026-02-01'),
(105,3,20,'paid','2026-02-02'),
(106,NULL,5,'paid','2026-02-03');An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.
The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.
How Pivoting and conditional aggregation works
Choose the output grain and category list first. One row per customer means grouping by customer identity. Paid and pending are the fixed output columns. CASE tests each sale’s category and supplies a value to the appropriate aggregate. SUM of one for a matching category and zero for a nonmatch counts category occurrences. Unlike a plain GROUP BY customer_id,status, the result places several category measures beside one another.
Start from the full customer dimension with a LEFT JOIN when customers with no sales must appear. The placeholder for Dev has no status, so both status CASE expressions choose zero. This yields a complete report rather than omitting Dev. The unlinked sale belongs to no identified customer, so it is not included in these customer-level measures. A separate anonymous row would require an explicit reporting design.
Counts, sums and missing values in a pivot
Counts and sums need different missing-value policies. A missing amount still belongs to a real pending sale and should count as one pending record. A SUM of pending amounts can be unknown or a known-value subtotal depending on the expression and nonmatching rows. Include a missing-amount count when readers need to know that a monetary category is incomplete. A zero known subtotal does not necessarily establish that every transaction in the category had a zero amount.
Fixed-category conditional aggregation is portable and keeps the output contract stable. Dynamic pivoting, where new status values create new column names, requires engine-specific dynamic SQL or application-side reshaping. Dynamically generated identifiers must be validated and quoted through the correct dialect mechanism; parameter placeholders ordinarily bind values rather than table or column names. A new unrecognised category can disappear from a fixed pivot unless you add an other count or monitor the distinct source categories.
MySQL, PostgreSQL, SQL Server and SQLite
CASE-based conditional aggregation works in all four systems. SQL Server has a dedicated PIVOT operator with its own syntax; PostgreSQL crosstab is supplied by an extension rather than the same built-in portable operator. MySQL and SQLite commonly use conditional aggregation for fixed pivots. Dynamic category-to-identifier generation requires a separate local implementation.
Check the engine-specific syntax and version requirements in SQL Server PIVOT, PostgreSQL CASE expressions.
Practice task and worked solution
Add a missing_amounts column to the customer report. Count actual matched sales with a missing amount, without counting Dev’s outer-join placeholder.
Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.
SELECT c.id,c.name,
SUM(CASE WHEN s.id IS NOT NULL AND s.amount IS NULL THEN 1 ELSE 0 END) AS missing_amounts
FROM customers AS c LEFT JOIN sales AS s ON s.customer_id=c.id
GROUP BY c.id,c.name ORDER BY c.id;id | name | missing_amounts
1 | Ana | 0
2 | Ben | 0
3 | Cara | 1
4 | Dev | 0The sales.id presence condition distinguishes real incomplete sales from no sale at all. Cara has one matched record with a missing amount. Dev’s placeholder has a missing amount too, but no sale id, so it correctly contributes zero to the data-quality count.
Add status to GROUP BY and observe separate rows rather than one customer pivot row. Remove it and restore category tests inside CASE. Next remove s.id IS NOT NULL from the missing-amount exercise: Dev’s placeholder would be counted as incomplete data. Restore the key-presence guard to keep no transaction distinct from a real transaction with an unknown amount.
SQL Pivoting with CASE: Turn Categories into Columns Syntax
Pivoting and conditional aggregation
SELECT c.id,c.name,
SUM(CASE WHEN s.status='paid' THEN 1 ELSE 0 END) AS paid_count,
SUM(CASE WHEN s.status='pending' THEN 1 ELSE 0 END) AS pending_count
FROM customers AS c LEFT JOIN sales AS s ON s.customer_id=c.id
GROUP BY c.id,c.name ORDER BY c.id;SQL Pivoting with CASE: Turn Categories into Columns Summary
- A pivot turns category values in rows, such as statuses, into separate columns.
- Group by the row identity you want, such as customer, and write one
SUM(CASE ...)orCOUNT(CASE ...)per category column. LEFT JOINfrom the full list of customers so those with no rows still appear with zeros.- The category columns are fixed in the query; a new category needs a new column or an "other" column.
- SQL Server's
PIVOTand PostgreSQL'scrosstabare dialect alternatives;CASEworks everywhere.
SQL Pivoting with CASE: Turn Categories into Columns Examples
Each example shows the code, the output it produces and what each part does.
Paid and pending counts per customer
Each status becomes a selected measurement column. Cara’s missing-amount pending sale still counts as a pending record. Dev remains because the customer dimension is preserved, and both of his conditional counts are zero.
SELECT c.id,c.name,
SUM(CASE WHEN s.status='paid' THEN 1 ELSE 0 END) AS paid_count,
SUM(CASE WHEN s.status='pending' THEN 1 ELSE 0 END) AS pending_count
FROM customers AS c LEFT JOIN sales AS s ON s.customer_id=c.id
GROUP BY c.id,c.name ORDER BY c.id;id | name | paid_count | pending_count
1 | Ana | 1 | 1
2 | Ben | 1 | 0
3 | Cara | 1 | 1
4 | Dev | 0 | 0Common SQL Pivoting with CASE: Turn Categories into Columns Mistakes
Grouping by the category being pivoted
Grouping by status as well as customer creates separate status rows rather than the desired one-row pivot. Another mistake is using COUNT(CASE ... ELSE 0 END), which counts nonmatching zeros too. Use SUM of one and zero or COUNT with a NULL nonmatch.
SQL Pivoting with CASE: Turn Categories into Columns in Real Projects
Side-by-side activity in a dashboard
A customer service dashboard can place paid and pending activity side by side while retaining customers with no purchases. Maintain the list of displayed categories as part of the report contract. Add completeness indicators when a monetary measure can exclude unknown amounts, rather than relying on a reassuring-looking zero.
Maintain a fixed pivot’s category vocabulary as part of its report contract. New statuses do not automatically create new columns and can disappear unless monitored through an other bucket or distinct-category check. Pair monetary columns with missing-input indicators where needed, so a known subtotal of zero is not presented as proof of complete zero-valued activity.
SQL Pivoting with CASE: Turn Categories into Columns FAQ
Does pivoting require a PIVOT keyword?
No. Conditional aggregation provides a portable fixed-category approach that works in every database. SQL Server's PIVOT operator is a shorter alternative there, but it still needs the category values listed in the query.
Can a fixed pivot show a newly added status automatically?
Not as a new column, because the columns are written into the query. Update the report contract when categories change, or include an explicit "other" column that counts anything not listed, so new values can't silently disappear.
Why check a joined id when counting missing amounts?
It avoids classifying an unmatched outer-join placeholder as an incomplete real transaction. A customer with no sales also has a NULL amount after the join, so test s.id IS NOT NULL AND s.amount IS NULL to count only real sales with missing amounts.
How do I unpivot columns back into rows?
Combine one SELECT per column with UNION ALL: SELECT id, 'paid' AS status, paid_count AS n FROM report UNION ALL SELECT id, 'pending', pending_count FROM report. SQL Server also has an UNPIVOT operator, and PostgreSQL can use a LATERAL join with VALUES.
Should pivoting happen in SQL or in the application?
For a known, small set of categories, pivoting in SQL keeps the report self-contained. When categories change often or there are many of them, return tidy rows, one per customer and status, and let the spreadsheet, BI tool or application build the columns.
SQL Pivoting with CASE: Turn Categories into Columns Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
What missing_amounts value is reported for Dev by the guarded exercise?
SQL Pivoting with CASE: Turn Categories into Columns Quiz
2 questions to check what you learned.
What grouping grain gives one pivot row per customer?
- Customer identity and status as separate grouping dimensions
- Only status, which merges different customers into status rows
- Customer identity, with status tested inside CASE
Why include s.id IS NOT NULL in the missing-amount count?
- To distinguish a real incomplete sale from an outer-join placeholder
- To force every unknown amount to become a stored zero
- To exclude every sale that has a known id and amount