Forgly

SQL to pivot rows into columns

Pivot rows to columns in SQL — portable conditional aggregation for MySQL, PostgreSQL, and SQLite, plus the native SQL Server PIVOT operator and PostgreSQL crosstab().

MySQL, PostgreSQL, SQLite — conditional aggregation (portable)
SELECT
  product,
  SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN amount ELSE 0 END) AS q2,
  SUM(CASE WHEN quarter = 'Q3' THEN amount ELSE 0 END) AS q3,
  SUM(CASE WHEN quarter = 'Q4' THEN amount ELSE 0 END) AS q4
FROM sales
GROUP BY product;

MySQL has no PIVOT keyword, so this CASE-based pattern is the standard way to pivot rows to columns in MySQL. It also runs unchanged on PostgreSQL and SQLite.

SQL Server (MSSQL) — native PIVOT operator
SELECT product, [Q1], [Q2], [Q3], [Q4]
FROM (
  SELECT product, quarter, amount FROM sales
) AS src
PIVOT (
  SUM(amount) FOR quarter IN ([Q1], [Q2], [Q3], [Q4])
) AS pvt;

SQL Server's PIVOT operator needs an explicit column list. For a dynamic set of columns, build the IN (...) list with dynamic SQL.

PostgreSQL — crosstab() from the tablefunc extension
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
  'SELECT product, quarter, SUM(amount)
   FROM sales
   GROUP BY product, quarter
   ORDER BY 1, 2',
  $$VALUES ('Q1'), ('Q2'), ('Q3'), ('Q4')$$
) AS ct(product text, q1 numeric, q2 numeric, q3 numeric, q4 numeric);

crosstab() requires the source query to be ordered by the row and category columns, and you must declare the output column types.

How to pivot rows to columns

Pivoting reshapes long data (one row per category) into wide data (one column per category). Conditional aggregation works in every dialect; SQL Server adds a native PIVOT operator and PostgreSQL has crosstab().

How it works

  • Conditional aggregation: each CASE keeps only the amount for one quarter and zeroes out the rest, then SUM collapses them into one row per product. This is how you pivot in MySQL and SQLite, which have no PIVOT keyword.
  • GROUP BY product produces one output row per product, with a column per quarter.
  • SQL Server's PIVOT operator does the same reshaping with dedicated syntax, but you must list the target columns explicitly inside IN (...).
  • PostgreSQL can use conditional aggregation too, or crosstab() from the tablefunc extension when you want a more pivot-like syntax.

Tip

All three approaches need a fixed, known set of output columns. For an unknown number of categories, generate the column list dynamically (dynamic SQL) and run the query in a second step.

Related SQL queries

Frequently asked questions

How do I pivot rows to columns in SQL?

Pivoting reshapes long data (one row per category) into wide data (one column per category). Conditional aggregation works in every dialect; SQL Server adds a native PIVOT operator and PostgreSQL has crosstab(). Conditional aggregation: each CASE keeps only the amount for one quarter and zeroes out the rest, then SUM collapses them into one row per product. This is how you pivot in MySQL and SQLite, which have no PIVOT keyword. GROUP BY product produces one output row per product, with a column per quarter. SQL Server's PIVOT operator does the same reshaping with dedicated syntax, but you must list the target columns explicitly inside IN (...). PostgreSQL can use conditional aggregation too, or crosstab() from the tablefunc extension when you want a more pivot-like syntax.

Does this work in PostgreSQL, MySQL, SQL Server, and SQLite?

Yes — this page lists the query for each dialect, since the syntax can differ between database engines.

Can I generate this query for my own tables?

Yes. Describe what you want in plain English with Forgly's free AI SQL Generator and it writes the query for your dialect.