How do you turn rows into columns in SQL?
You turn rows into columns with conditional aggregation, which means a counting function with a condition inside it.
The shape is always the same. You write one column in the select list for each value that you want to become a column, and inside each one you count the rows that match that value.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
order_date TEXT NOT NULL,
status VARCHAR(20) NOT NULL
);
INSERT INTO orders (id, customer_id, order_date, status) VALUES
(1, 1, '2026-01-05', 'paid'),
(2, 1, '2026-01-09', 'cancelled'),
(3, 2, '2026-01-11', 'paid');
-- The orders for each month, with the statuses as columns.
SELECT
SUBSTR(order_date, 1, 7) AS month,
COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid,
COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled,
COUNT(CASE WHEN status = 'refunded' THEN 1 END) AS refunded
FROM orders
GROUP BY month
ORDER BY month;
month paid cancelled refunded
2026-01 2 1 0
So what is the trick? The function COUNT counts the rows where the value inside it is not missing. So the case expression gives back 1 for the rows that match and nothing for the rows that do not, and the count picks up only the ones that matched.
There are two details that are worth knowing.
The first one is that the ELSE is left out on purpose. If you put ELSE 0 in there, then the count is the same, because the zero is not missing and it would be counted. So leaving it out is the way to get a zero in the answer rather than a missing value.
The second one is that the same shape works with a sum. The expression SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) gives you the money from the paid orders.
The next two questions in this topic put it to work and look at the limits.