How does the pivot rows to columns query work?

Each output column is a conditional aggregate that only sees the rows for one quarter. GROUP BY region keeps one row per region, so the quarters line up side by side. The date filter keeps the table to a single year, otherwise the same quarter from different years would be added together.

What commonly goes wrong with pivot rows to columns in SQL?

The columns are fixed in the query; a new category needs a new column, so dynamic pivots are usually done in the reporting tool.

Pivot Rows to Columns in SQL

Pivoting turns the values of one column into separate columns, such as one column per quarter, so a table reads like a spreadsheet.

Example query

This example assumes: orders(id, region text, order_date date, total_amount numeric). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT region,
       SUM(total_amount) FILTER (WHERE EXTRACT(QUARTER FROM order_date) = 1) AS q1,
       SUM(total_amount) FILTER (WHERE EXTRACT(QUARTER FROM order_date) = 2) AS q2,
       SUM(total_amount) FILTER (WHERE EXTRACT(QUARTER FROM order_date) = 3) AS q3,
       SUM(total_amount) FILTER (WHERE EXTRACT(QUARTER FROM order_date) = 4) AS q4,
       SUM(total_amount) AS year_total
FROM orders
WHERE order_date >= DATE '2026-01-01'
  AND order_date <  DATE '2027-01-01'
GROUP BY region
ORDER BY region;

How it works

Each output column is a conditional aggregate that only sees the rows for one quarter.
GROUP BY region keeps one row per region, so the quarters line up side by side.
The date filter keeps the table to a single year, otherwise the same quarter from different years would be added together.

MySQL, BigQuery, SQL Server and SQLite differences

SQL Server and Oracle have a PIVOT operator, and BigQuery has a PIVOT clause. In PostgreSQL the tablefunc extension offers crosstab, but conditional aggregation needs no extension.
MySQL and others use SUM(CASE WHEN ...) in place of FILTER.

Common mistakes

The columns are fixed in the query; a new category needs a new column, so dynamic pivots are usually done in the reporting tool.
A year filter is easy to forget, and then Q1 mixes several years.
Pivoted tables are hard to chart or join; keep the long format for analysis and pivot at the end.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

Show sales by region with one column per quarter for 2026.
Give me a table of ticket counts by priority and by month.