Reporting questions rarely want a vertical list. A manager asks 'how many active, completed, and on-hold projects does each department have?' — a question with three answers hiding in a single status column. Pivoting turns those rows into columns so the result fits on one line per department, making it easy to read and compare. The technique is conditional aggregation: place a CASE expression inside SUM or COUNT so each column totals only the rows that match its condition. In this lesson you will build cross-tabulations, sum conditionally, compute percentages, and learn when to pivot in the database instead of exporting to a spreadsheet.
flowchart TD B["Before: one row per project\nstatus = active"] --> A["After: one row per department\nactive_count | completed_count"] style A fill:#0e7490,color:#fff
The anatomy of a pivot
A pivot query has three parts. The row label — usually a GROUP BY column like department name — defines what each result row represents. The aggregate — SUM, COUNT, or AVG — does the math. And the CASE expression inside the aggregate decides which rows contribute to which column. The result is a cross-tabulation: one row per group and one column per category.
For example, to count active projects per department, you write COUNT(CASE WHEN status = 'active' THEN 1 END). The CASE returns 1 for active rows and NULL for everything else; COUNT ignores NULLs, so only active rows are tallied. Repeat the pattern for each status you need, and the result is a clean summary with no missing departments.
SELECT d.name, COUNT(CASE WHEN p.status = 'active' THEN 1 END) AS active_count, COUNT(CASE WHEN p.status = 'completed' THEN 1 END) AS completed_count, COUNT(CASE WHEN p.status = 'on hold' THEN 1 END) AS on_hold_count FROM departments d LEFT JOIN projects p ON d.id = p.department_id GROUP BY d.name;
Conditional sums and averages
COUNT is useful for tallying rows, but many questions are about money, time, or scores. To pivot a numeric column, switch from COUNT to SUM and return the value itself inside the CASE instead of 1. SUM(CASE WHEN status = 'active' THEN budget ELSE 0 END) adds the budgets of every active project and returns 0 for the rest, so the total is never NULL.
You can also pivot averages with AVG(CASE WHEN condition THEN value END). Unlike SUM, AVG ignores NULLs automatically, so omitting ELSE is safe and often clearer. The rule is simple: use COUNT when you care about how many, SUM when you care about how much, and AVG when you care about the typical value. Always match the aggregate to the question being asked.
flowchart LR R1["Row: active, budget 600000"] --> C1["CASE returns 600000"] --> S1["SUM adds 600000"] R2["Row: on hold, budget 150000"] --> C2["CASE returns NULL"] --> S2["SUM ignores NULL"] style S1 fill:#0e7490,color:#fff style S2 fill:#b45309,color:#fff
SELECT d.name, SUM(CASE WHEN p.status = 'active' THEN p.budget ELSE 0 END) AS active_budget, SUM(CASE WHEN p.status = 'completed' THEN p.budget ELSE 0 END) AS completed_budget, SUM(CASE WHEN p.status = 'on hold' THEN p.budget ELSE 0 END) AS on_hold_budget FROM departments d LEFT JOIN projects p ON d.id = p.department_id GROUP BY d.name;
Adding row totals and percentages
A pivot becomes more useful when you add a total column. Because the database computes every aggregate in the same pass, you can write SUM(budget) AS total_budget alongside your conditional sums and get the grand total for free. Dividing a conditional sum by the total gives a percentage: 100.0 * SUM(CASE WHEN status = 'active' THEN budget END) / SUM(budget) AS active_pct.
Be careful with integer division: multiply by 100.0 before dividing, or CAST the numerator to REAL, so SQLite returns a decimal rather than truncating to zero. This pattern turns a raw pivot into a dashboard-ready metric without leaving the database.
flowchart TD D1["Engineering rows"] --> G1["GROUP BY"] --> A1["active=2\ncompleted=1"] D2["Marketing rows"] --> G1 style A1 fill:#0e7490,color:#fff
SELECT d.name, SUM(p.budget) AS total_budget, ROUND(100.0 * SUM(CASE WHEN p.status = 'active' THEN p.budget END) / SUM(p.budget), 1) AS active_pct FROM departments d LEFT JOIN projects p ON d.id = p.department_id GROUP BY d.name;
When to pivot in SQL
Small cross-tabulations belong in the query layer because they keep the data and presentation logic together. If the report fits in a single screen and the categories are stable, conditional aggregation is the right tool. But if the categories change frequently, if the user can drag and drop columns, or if the pivot has dozens of dimensions, the application layer is usually a better place to reshape the data. SQL pivots are rigid: every column must be named explicitly, and adding a new category means editing the query. Use SQL for the heavy lifting — filtering, joining, and aggregating — and export the raw grouped data when the layout needs to be dynamic.
Practice: pivoting in action
The exercises ask you to reshape project data by status, then by budget, and finally build a report that slices employees by salary band within each department. The transfer task combines a join, conditional aggregation, and careful range logic — nothing in the lesson shows the exact query, so you will need to assemble the pieces yourself.
Pivot the project data to show one row per department. Return department_name, active_count, and completed_count. Use conditional aggregation with COUNT and join to departments to get the name. Group by department name.
SELECT d.name AS department_name, -- count active projects here -- count completed projects here FROM departments d LEFT JOIN projects p ON d.id = p.department_id GROUP BY d.name;
Use COUNT(CASE WHEN p.status = 'active' THEN 1 END) for active_count.
Join departments to projects so you can group by department name.
SELECT d.name AS department_name, COUNT(CASE WHEN p.status = 'active' THEN 1 END) AS active_count, COUNT(CASE WHEN p.status = 'completed' THEN 1 END) AS completed_count FROM departments d LEFT JOIN projects p ON d.id = p.department_id GROUP BY d.name;
Show each department's name and the total budget of its active projects. Use conditional SUM, not COUNT. Call the column active_budget.
SELECT d.name, -- sum active budgets here AS active_budget FROM departments d JOIN projects p ON d.id = p.department_id GROUP BY d.name;
Use SUM(CASE WHEN p.status = 'active' THEN p.budget ELSE 0 END).
Remember to use SUM, not COUNT, when adding numeric values.
SELECT d.name, SUM(CASE WHEN p.status = 'active' THEN p.budget ELSE 0 END) AS active_budget FROM departments d JOIN projects p ON d.id = p.department_id GROUP BY d.name;
This query is meant to show the total active budget per department, but it uses COUNT instead of SUM and returns the number of active projects rather than their total budget. Fix it.
SELECT d.name, COUNT(CASE WHEN p.status = 'active' THEN p.budget END) AS active_budget FROM departments d JOIN projects p ON d.id = p.department_id GROUP BY d.name;
COUNT counts non-NULL values; it does not add them.
Use SUM(CASE WHEN ... THEN p.budget ELSE 0 END) to add the budgets.
SELECT d.name, SUM(CASE WHEN p.status = 'active' THEN p.budget ELSE 0 END) AS active_budget FROM departments d JOIN projects p ON d.id = p.department_id GROUP BY d.name;
Create a report that shows each department's name and three columns counting employees by salary band: high_earners (salary >= 700000), mid_earners (salary >= 500000 AND salary < 700000), and low_earners (salary < 500000). Join employees to departments and group by department name.
SELECT d.name, -- count high earners -- count mid earners -- count low earners FROM departments d JOIN employees e ON d.id = e.department_id GROUP BY d.name;
Use COUNT(CASE WHEN e.salary >= 700000 THEN 1 END) for high_earners.
For mid_earners, the condition is salary >= 500000 AND salary < 700000 to avoid double-counting.
Use a LEFT JOIN if you want departments with no employees, but the dataset gives every department at least one.
SELECT d.name, COUNT(CASE WHEN e.salary >= 700000 THEN 1 END) AS high_earners, COUNT(CASE WHEN e.salary >= 500000 AND e.salary < 700000 THEN 1 END) AS mid_earners, COUNT(CASE WHEN e.salary < 500000 THEN 1 END) AS low_earners FROM departments d JOIN employees e ON d.id = e.department_id GROUP BY d.name;
What is the result of SUM(CASE WHEN status = 'active' THEN budget END) for rows where status is not 'active'?
Without an ELSE, CASE returns NULL for non-matching rows. SUM ignores NULLs, so they contribute nothing to the total. This is usually what you want. With COUNT, the behavior would be different because NULLs are not counted.
Recap
- Pivoting reshapes row-level categories into summary columns using conditional aggregation.
- COUNT(CASE WHEN ... THEN 1 END) tallies rows; SUM(CASE WHEN ... THEN value END) adds numeric values.
- Always match the aggregate to the question: COUNT for how many, SUM for how much, AVG for the typical.
- Use GROUP BY to create the row labels and LEFT JOIN to include groups that might have zero matches.
- Add a total column with an unconditional aggregate, and compute percentages by dividing the conditional sum by the total.
- Pivot in SQL when the layout is stable; move dynamic reshaping to the application layer.
Next you will combine AND, OR, IN, BETWEEN, LIKE, and NULL tests into precise filtering logic that can handle real-world edge cases.