SQL Pivot Generator (Rows to Columns)

Turn rows into columns without hand-writing a dozen aggregates. Enter your table, the column(s) that identify each output row, the category column to spread across the page, and the value to aggregate — the generator writes the pivot query for you in three flavours: a PostgreSQL FILTER (WHERE ...) query, a portable CASE WHEN form that runs on MySQL, SQL Server and SQLite, and the classic PostgreSQL crosstab() call with the tablefunc setup it needs. It also produces the reverse unpivot (columns back to rows) with LATERAL VALUES. Everything runs in your browser; nothing is uploaded.

TableRow column(s)Category column

Category values → become columns (comma or newline separated)

Value columnAggregateDialectcrosstab value typeRow total column

Pivot query (FILTER / CASE)

SELECT
  region,
  SUM(amount) FILTER (WHERE quarter = 'Q1') AS q1,
  SUM(amount) FILTER (WHERE quarter = 'Q2') AS q2,
  SUM(amount) FILTER (WHERE quarter = 'Q3') AS q3,
  SUM(amount) FILTER (WHERE quarter = 'Q4') AS q4,
  SUM(amount) AS total
FROM public.sales
GROUP BY region
ORDER BY region;

PostgreSQL crosstab() version

-- Requires the tablefunc extension (once per database):
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
  -- source query: row id, category, value (must be ORDER BY 1, 2)
  $$ SELECT region, quarter, SUM(amount)
     FROM public.sales
     GROUP BY 1, 2
     ORDER BY 1, 2 $$,
  -- category list: fixes column order and fills gaps with NULL
  $$ VALUES ('Q1'), ('Q2'), ('Q3'), ('Q4') $$
) AS ct (
  region text,
  q1 numeric,
  q2 numeric,
  q3 numeric,
  q4 numeric
);

Unpivot (columns → rows)

-- Reverse operation (columns back to rows) with a LATERAL VALUES join:
SELECT region, v.quarter, v.amount
FROM pivoted_table p
CROSS JOIN LATERAL (
  VALUES
    ('Q1', p.q1),
    ('Q2', p.q2),
    ('Q3', p.q3),
    ('Q4', p.q4)
) AS v (quarter, amount);

Notes

• FILTER (WHERE ...) is standard SQL:2003 aggregate filtering; PostgreSQL, SQLite and DuckDB support it. For MySQL or SQL Server switch the dialect to the portable CASE form.
• Category values are hard-coded because SQL requires the column list to be known before execution. For fully dynamic categories, build this statement in application code (or plpgsql EXECUTE) from SELECT DISTINCT quarter.
• crosstab() with one query argument drops missing category cells and can misalign columns; always use the two-argument form shown here so every output row has the same column order.
• COUNT with FILTER/CASE counts only matching rows; SUM over an empty match returns NULL, not 0 — wrap in COALESCE(expr, 0) if you need zeros.

Do more than sql pivot generator (rows to columns) — meet Chat2DB

Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.

How to use

Enter the table, the row identifier column(s), the category column, and the list of category values that should become columns.
Pick the value column and aggregate (SUM, COUNT, AVG, MIN, MAX) and choose PostgreSQL FILTER or the portable CASE dialect.
Copy the generated pivot query, the crosstab() version, or the unpivot statement for the reverse direction.

Frequently asked questions

How do I pivot rows to columns in SQL?

Use one conditional aggregate per output column: SELECT region, SUM(amount) FILTER (WHERE quarter = 'Q1') AS q1, SUM(amount) FILTER (WHERE quarter = 'Q2') AS q2 FROM sales GROUP BY region. On databases without FILTER, write SUM(CASE WHEN quarter = 'Q1' THEN amount END) instead. Each aggregate only 'sees' rows from its category, so one GROUP BY row becomes one output row with a column per category.

What is crosstab() in PostgreSQL and when should I use it?

crosstab() comes from the tablefunc extension (CREATE EXTENSION tablefunc) and returns a pivoted record set from a 'row, category, value' query. Prefer the two-argument form, which takes an explicit category list so columns stay aligned even when data is missing. It keeps the query short when you have many categories, but the FILTER/CASE approach is usually easier to read, needs no extension, and supports multiple row columns and several aggregates at once.

Can SQL pivot with a dynamic list of columns?

Not directly — SQL requires every output column to be known before the query runs, so the category list must be hard-coded. For dynamic pivots, first run SELECT DISTINCT category FROM t, then build the pivot statement in application code or in a plpgsql function with EXECUTE. Chat2DB's AI SQL editor can also write and run these pivot queries against your live schema — download it at https://chat2db.ai/download or use the web version at https://app.chat2db.ai.