Schema and sample data
| sale_id | region | month | amount |
|---|---|---|---|
| 1 | North | Jan | 120.00 |
| 2 | North | Feb | 200.00 |
| 3 | North | Mar | 150.00 |
| 4 | South | Jan | 300.00 |
| 5 | South | Feb | 250.00 |
| 6 | South | Mar | 400.00 |
| 7 | West | Jan | 90.00 |
| 8 | West | Feb | 60.00 |
Show setup SQL (copy to run)
CREATE TABLE Sales (
sale_id INT PRIMARY KEY,
region VARCHAR(20) NOT NULL,
month VARCHAR(3) NOT NULL,
amount DECIMAL(10,2) NOT NULL
);
INSERT INTO Sales (sale_id, region, month, amount) VALUES
(1, 'North', 'Jan', 120.00),
(2, 'North', 'Feb', 200.00),
(3, 'North', 'Mar', 150.00),
(4, 'South', 'Jan', 300.00),
(5, 'South', 'Feb', 250.00),
(6, 'South', 'Mar', 400.00),
(7, 'West', 'Jan', 90.00),
(8, 'West', 'Feb', 60.00);The Sales table stores one row per sale, with a region, a three letter month ('Jan', 'Feb' or 'Mar') and an amount. Return one row per region with a separate column for each month - Jan, Feb and Mar - holding that region total for the month. Sort the result by region.
Expected result
| region | Jan | Feb | Mar |
|---|---|---|---|
| North | 120.00 | 200.00 | 150.00 |
| South | 300.00 | 250.00 | 400.00 |
| West | 90.00 | 60.00 | NULL |
Show hint
Turning rows into columns is a pivot. The portable way is conditional aggregation: for each target column, sum the amount only for the rows that belong in it, using SUM(CASE WHEN month = 'Jan' THEN amount END), then GROUP BY region so you collapse to one row per region.
Try to write the query yourself before you open this.
Show solution and explanation
SELECT
region,
SUM(CASE WHEN month = 'Jan' THEN amount END) AS Jan,
SUM(CASE WHEN month = 'Feb' THEN amount END) AS Feb,
SUM(CASE WHEN month = 'Mar' THEN amount END) AS Mar
FROM Sales
GROUP BY region
ORDER BY region;The idea is conditional aggregation, the most portable way to pivot rows into columns. Each CASE WHEN month = 'Jan' THEN amount END expression yields the amount when the row is a January sale and NULL otherwise. Wrapping it in SUM(...) adds up only the matching rows, because aggregate functions ignore NULL. Repeating that once per month gives one column per month, and GROUP BY region collapses the input to a single row per region.
Row by row: North totals 120, 200 and 150 for Jan, Feb and Mar; South totals 300, 250 and 400. West has no March sale, so SUM(CASE ...) for Mar sees no matching rows and returns NULL. If you would rather show a zero there, add an ELSE 0 inside the CASE or wrap the whole expression in COALESCE(SUM(...), 0). See pivot rows to columns for more on this pattern.
SQL Server also has a dedicated PIVOT operator that expresses the same result:
SELECT region, [Jan], [Feb], [Mar]\nFROM (SELECT region, month, amount FROM Sales) AS src\nPIVOT (SUM(amount) FOR month IN ([Jan], [Feb], [Mar])) AS pvt\nORDER BY region;It is compact, but the month names must be hard coded in the IN list just like the CASE version, and the syntax is SQL Server specific. The conditional aggregation form works on every database and is easier to extend with extra logic, so many people reach for it first. A common mistake is to forget GROUP BY region, which either errors or, if you also select non aggregated columns incorrectly, returns one row per sale instead of one per region.