By Matthew Rathbone · November 15, 2026

To pivot in SQL you turn the distinct values of one column (say, Q1, Q2, Q3, Q4) into separate columns and fill each one with an aggregate such as SUM. The method that works in every database is GROUP BY plus one CASE expression per new column:

SELECT region,
       SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS q1,
       SUM(CASE WHEN quarter = 'Q2' THEN amount END) AS q2,
       SUM(CASE WHEN quarter = 'Q3' THEN amount END) AS q3,
       SUM(CASE WHEN quarter = 'Q4' THEN amount END) AS q4
FROM sales
GROUP BY region;

SQL Server, Oracle, Snowflake, Databricks and DuckDB also have a PIVOT operator that does the same job with shorter syntax. PostgreSQL has crosstab(), and MySQL and SQLite have no pivot keyword at all, so you use the CASE version.

Jump to: what pivoting is · sample data · CASE pivot (any database) · FILTER · PIVOT operator · PostgreSQL crosstab · MySQL · multiple columns · totals and percentages · dynamic pivot · UNPIVOT · by database · mistakes · FAQ

What does PIVOT do in SQL?

Most tables store data "long": one row per event, with a column that says which category the row belongs to. A report usually wants it "wide": one row per thing you care about, and one column per category. Pivoting is the conversion from long to wide. Unpivoting is the reverse, turning columns back into rows.

Here is the same data both ways. Long, one row per region and quarter:

region | quarter | total
-------|---------|------
East   | Q1      | 1500
East   | Q2      | 900
East   | Q3      | 1500
East   | Q4      | 450
North  | Q2      | 200
North  | Q3      | 700
West   | Q1      | 800
West   | Q2      | 1450
West   | Q4      | 1300

Wide, after pivoting quarter into columns:

region | q1   | q2   | q3   | q4
-------|------|------|------|-----
East   | 1500 | 900  | 1500 | 450
North  | NULL | 200  | 700  | NULL
West   | 800  | 1450 | NULL | 1300

If you have built a pivot table in Excel or Google Sheets, this is the same idea: region is the row field, quarter is the column field, and SUM(amount) is the value field. Every SQL pivot needs those three pieces:

  1. Row heading: the column(s) you GROUP BY, one output row per value.
  2. Column heading: the column whose values become new columns.
  3. Value: an aggregate (SUM, COUNT, AVG, MAX...) that fills each cell.

The one rule that surprises people: SQL needs to know the output columns before the query runs. You list Q1 to Q4 yourself. If a Q5 shows up next year, the query will not grow a new column on its own. The dynamic pivot section shows the workaround.

Sample data

Every example uses this small sales table. The CASE and UNION ALL queries were run on SQLite 3.45, the FILTER, crosstab and LATERAL queries on PostgreSQL 16, and the PIVOT/UNPIVOT operator queries on DuckDB 2.0, which accepts the same PIVOT (... FOR ... IN ...) form as Oracle and Snowflake. Output is copied from the real results.

CREATE TABLE sales (id INTEGER PRIMARY KEY, region TEXT, quarter TEXT,
                    product TEXT, amount INTEGER);
INSERT INTO sales VALUES
 (1,  'East',  'Q1', 'Laptop',  1200),
 (2,  'East',  'Q1', 'Monitor',  300),
 (3,  'East',  'Q2', 'Laptop',   900),
 (4,  'East',  'Q3', 'Laptop',  1500),
 (5,  'East',  'Q4', 'Monitor',  450),
 (6,  'West',  'Q1', 'Laptop',   800),
 (7,  'West',  'Q2', 'Monitor',  350),
 (8,  'West',  'Q2', 'Laptop',  1100),
 (9,  'West',  'Q4', 'Laptop',  1300),
 (10, 'North', 'Q2', 'Monitor',  200),
 (11, 'North', 'Q3', 'Laptop',   700);

Note the gaps: North sold nothing in Q1 or Q4, and West sold nothing in Q3. Gaps are where pivots most often go wrong.

Pivot rows to columns with CASE (works in every database)

This is the method to learn first. It is plain SQL, it runs in MySQL, PostgreSQL, SQL Server, Oracle, SQLite and every data warehouse, and it is often called conditional aggregation.

Step 1: group by the row heading

Start with a normal GROUP BY so you get one row per region:

SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region;

Step 2: add one CASE column per pivot value

Inside the aggregate, a CASE expression passes through the amount only when the row belongs to that column's quarter. Every other row contributes 0, so the SUM adds up just that quarter:

SELECT region,
       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,
       SUM(amount) AS total
FROM sales
GROUP BY region
ORDER BY region;
region | q1   | q2   | q3   | q4   | total
-------|------|------|------|------|------
East   | 1500 | 900  | 1500 | 450  | 4350
North  | 0    | 200  | 700  | 0    | 900
West   | 800  | 1450 | 0    | 1300 | 3550

East's Q1 is 1500 because two East rows (1200 + 300) landed in that cell. The pivot aggregates, it does not just move values around. A total column comes for free because it is just another aggregate in the same GROUP BY.

ELSE 0 or no ELSE: zeros vs NULLs

Leave out ELSE 0 and the non-matching rows become NULL, which SUM ignores. If a cell has no rows at all, it comes back NULL instead of 0:

SELECT region,
       SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS q1,
       SUM(CASE WHEN quarter = 'Q2' THEN amount END) AS q2,
       SUM(CASE WHEN quarter = 'Q3' THEN amount END) AS q3,
       SUM(CASE WHEN quarter = 'Q4' THEN amount END) AS q4
FROM sales
GROUP BY region
ORDER BY region;
region | q1   | q2   | q3   | q4
-------|------|------|------|-----
East   | 1500 | 900  | 1500 | 450
North  | NULL | 200  | 700  | NULL
West   | 800  | 1450 | NULL | 1300

Both are correct. NULL says "no sales recorded", 0 says "sales added up to zero". Pick the one your report means. If you want NULL inside the query but 0 on screen, wrap the aggregate in COALESCE: COALESCE(SUM(CASE ... END), 0). Do not use ELSE 0 with AVG or MIN: the zeros would be included and drag the result down.

Counting instead of summing

To count rows per cell, return anything non-NULL when the condition matches and let COUNT skip the NULLs. Here products are rows and regions are columns:

SELECT product,
       COUNT(CASE WHEN region = 'East'  THEN 1 END) AS east,
       COUNT(CASE WHEN region = 'West'  THEN 1 END) AS west,
       COUNT(CASE WHEN region = 'North' THEN 1 END) AS north
FROM sales
GROUP BY product
ORDER BY product;
product | east | west | north
--------|------|------|------
Laptop  | 3    | 3    | 1
Monitor | 2    | 1    | 1

Avoid COUNT(CASE WHEN ... THEN 1 ELSE 0 END). COUNT counts the zeros too, so every cell would show the full group size. Use SUM(CASE WHEN ... THEN 1 ELSE 0 END) if you prefer the ELSE form.

Shorter syntax: the FILTER clause (PostgreSQL, SQLite)

PostgreSQL 9.4+ and SQLite 3.30+ support the SQL-standard FILTER clause, which reads more cleanly than a CASE inside every aggregate:

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
FROM sales
GROUP BY region
ORDER BY region;
 region |  q1  |  q2  |  q3  |  q4
--------+------+------+------+------
 East   | 1500 |  900 | 1500 |  450
 North  |      |  200 |  700 |
 West   |  800 | 1450 |      | 1300

(psql prints NULL as a blank.) The result is identical to the CASE version without ELSE. MySQL, SQL Server and Oracle do not support FILTER, so stick with CASE if your query needs to be portable.

The PIVOT operator (SQL Server, Oracle, Snowflake, Databricks, DuckDB)

These databases have a built-in PIVOT keyword. It produces exactly the same result as the CASE pivot. It is shorter when you have many columns, but it has one trap, covered below.

SQL Server PIVOT syntax

SELECT region, [Q1], [Q2], [Q3], [Q4]
FROM (
    SELECT region, quarter, amount     -- only the columns the pivot needs
    FROM sales
) AS src
PIVOT (
    SUM(amount)                        -- value: the aggregate
    FOR quarter                        -- column heading
    IN ([Q1], [Q2], [Q3], [Q4])        -- the values that become columns
) AS p
ORDER BY region;
region | Q1   | Q2   | Q3   | Q4
-------|------|------|------|-----
East   | 1500 | 900  | 1500 | 450
North  | NULL | 200  | 700  | NULL
West   | 800  | 1450 | NULL | 1300

Reading it piece by piece:

  • The subquery picks the rows and columns to feed in. Every column you do not mention in PIVOT becomes a row heading.
  • SUM(amount) is the value for each cell. You must use an aggregate, even if each cell will only ever hold one row (MAX is the usual choice for text).
  • FOR quarter IN ([Q1], ...) lists the values that become columns. In SQL Server they are written as identifiers in square brackets, not as quoted strings, and the output columns get those names.
  • Both aliases are required in SQL Server: one for the subquery (src) and one for the pivot (p).

Empty cells come back NULL, the same as the CASE version without ELSE. Use ISNULL([Q1], 0) AS Q1 in the select list to show zeros.

The extra-column trap

PIVOT has no GROUP BY. It groups by every column of the input that is not the pivot or value column. If you pivot the whole table, id and product become row headings too, and you get one sparse row per order instead of one row per region:

SELECT *
FROM sales
PIVOT (SUM(amount) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))
ORDER BY region;
id | region | product | Q1   | Q2   | Q3   | Q4
---|--------|---------|------|------|------|-----
5  | East   | Monitor | NULL | NULL | NULL | 450
4  | East   | Laptop  | NULL | NULL | 1500 | NULL
2  | East   | Monitor | 300  | NULL | NULL | NULL
3  | East   | Laptop  | NULL | 900  | NULL | NULL
1  | East   | Laptop  | 1200 | NULL | NULL | NULL
10 | North  | Monitor | NULL | 200  | NULL | NULL
...

This is the most common reason a PIVOT "doesn't group". The fix is always the same: select only the row heading, the pivot column and the value column in a subquery (or CTE) first, as in the syntax example above.

The same rule is how you get more than one row heading on purpose. Keep product in the subquery and it becomes a second grouping column:

SELECT *
FROM (SELECT region, product, quarter, amount FROM sales) AS src
PIVOT (SUM(amount) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))
ORDER BY region, product;
region | product | Q1   | Q2   | Q3   | Q4
-------|---------|------|------|------|-----
East   | Laptop  | 1200 | 900  | 1500 | NULL
East   | Monitor | 300  | NULL | NULL | 450
North  | Laptop  | NULL | NULL | 700  | NULL
North  | Monitor | NULL | 200  | NULL | NULL
West   | Laptop  | 800  | 1100 | NULL | 1300
West   | Monitor | NULL | 350  | NULL | NULL

(These two were run on DuckDB, which uses quoted strings in IN; in SQL Server write [Q1] instead. The grouping behaviour is the same.)

Oracle and Snowflake PIVOT

Oracle (11g+) and Snowflake use quoted values, and you can rename each output column with AS:

SELECT *
FROM (SELECT region, quarter, amount FROM sales)
PIVOT (
    SUM(amount)
    FOR quarter IN ('Q1' AS q1, 'Q2' AS q2, 'Q3' AS q3, 'Q4' AS q4)
)
ORDER BY region;

Snowflake also accepts IN (ANY ORDER BY quarter) to pick up the column values automatically. Databricks SQL uses the Oracle form. DuckDB accepts the Oracle form and also a short form that finds the columns for you: PIVOT sales ON quarter USING SUM(amount) GROUP BY region.

PostgreSQL pivot with crosstab()

PostgreSQL has no PIVOT keyword. The CASE or FILTER versions above are what most people use. The alternative is crosstab() from the tablefunc extension, which ships with PostgreSQL but must be enabled once per database:

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
  $$SELECT region, quarter, SUM(amount)
    FROM sales GROUP BY region, quarter ORDER BY 1, 2$$,
  $$VALUES ('Q1'), ('Q2'), ('Q3'), ('Q4')$$
) AS ct(region text, q1 bigint, q2 bigint, q3 bigint, q4 bigint);
 region |  q1  |  q2  |  q3  |  q4
--------+------+------+------+------
 East   | 1500 |  900 | 1500 |  450
 North  |      |  200 |  700 |
 West   |  800 | 1450 |      | 1300

The first query must return exactly three columns (row heading, column heading, value) sorted by the row heading. The second lists the column values in order. You also have to spell out the output column names and types in the AS ct(...) list.

Always use the two-argument form. The one-argument crosstab(sql) fills columns left to right with whatever values each row has, ignoring which quarter they belong to. With gaps in the data it silently shifts numbers into the wrong column:

-- one-argument form: WRONG when a row has gaps
SELECT * FROM crosstab(
  $$SELECT region, quarter, SUM(amount)
    FROM sales GROUP BY region, quarter ORDER BY 1, 2$$
) AS ct(region text, q1 bigint, q2 bigint, q3 bigint, q4 bigint);
 region |  q1  |  q2  |  q3  | q4
--------+------+------+------+-----
 East   | 1500 |  900 | 1500 | 450
 North  |  200 |  700 |      |
 West   |  800 | 1450 | 1300 |

North's Q2 sales now show up as Q1, and West's Q4 as Q3. No error, just wrong numbers. Given that trap and the extra setup, the FILTER version is usually the better choice in PostgreSQL.

How to pivot in MySQL (and SQLite)

MySQL and SQLite have no pivot operator. Use the CASE pivot. MySQL also lets you write IF() instead of CASE, which is a little shorter:

SELECT region,
       SUM(IF(quarter = 'Q1', amount, 0)) AS q1,
       SUM(IF(quarter = 'Q2', amount, 0)) AS q2,
       SUM(IF(quarter = 'Q3', amount, 0)) AS q3,
       SUM(IF(quarter = 'Q4', amount, 0)) AS q4
FROM sales
GROUP BY region;

In MySQL a comparison returns 1 or 0, so SUM(quarter = 'Q1') on its own counts the Q1 rows. It works, but CASE is easier for the next reader to understand.

Pivot multiple columns (more than one value per cell)

The PIVOT operator in SQL Server takes one aggregate. With the CASE method you can put as many aggregates side by side as you like. Here each product gets a sales column and an order-count column:

SELECT region,
       SUM(CASE WHEN product = 'Laptop'  THEN amount ELSE 0 END) AS laptop_sales,
       COUNT(CASE WHEN product = 'Laptop'  THEN 1 END)           AS laptop_orders,
       SUM(CASE WHEN product = 'Monitor' THEN amount ELSE 0 END) AS monitor_sales,
       COUNT(CASE WHEN product = 'Monitor' THEN 1 END)           AS monitor_orders
FROM sales
GROUP BY region
ORDER BY region;
region | laptop_sales | laptop_orders | monitor_sales | monitor_orders
-------|--------------|---------------|---------------|---------------
East   | 3600         | 3             | 750           | 2
North  | 700          | 1             | 200           | 1
West   | 3200         | 3             | 350           | 1

Oracle, Snowflake and DuckDB allow several aggregates inside one PIVOT (PIVOT (SUM(amount) AS sales, COUNT(*) AS orders FOR ...)). In SQL Server you would need two PIVOTs joined together, which is why most people switch to CASE at this point.

Add a totals row and percentage columns

A pivot is just a grouped query, so the usual tricks apply. For a grand-total row, UNION ALL the same query without the GROUP BY:

SELECT region,
       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 region
UNION ALL
SELECT 'All regions',
       SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END),
       SUM(CASE WHEN quarter = 'Q2' THEN amount ELSE 0 END),
       SUM(CASE WHEN quarter = 'Q3' THEN amount ELSE 0 END),
       SUM(CASE WHEN quarter = 'Q4' THEN amount ELSE 0 END)
FROM sales;
region      | q1   | q2   | q3   | q4
------------|------|------|------|-----
East        | 1500 | 900  | 1500 | 450
North       | 0    | 200  | 700  | 0
West        | 800  | 1450 | 0    | 1300
All regions | 2300 | 2550 | 2200 | 1750

PostgreSQL, SQL Server, Oracle and MySQL can produce the same total row with GROUP BY ROLLUP(region) (MySQL: GROUP BY region WITH ROLLUP), as shown in the GROUP BY guide.

For a share-of-total column, divide one pivot column by the row total. Multiply by 100.0 first so integer division does not round everything to 0:

SELECT region,
       ROUND(100.0 * SUM(CASE WHEN product = 'Laptop'  THEN amount ELSE 0 END) / SUM(amount), 1) AS laptop_pct,
       ROUND(100.0 * SUM(CASE WHEN product = 'Monitor' THEN amount ELSE 0 END) / SUM(amount), 1) AS monitor_pct
FROM sales
GROUP BY region
ORDER BY region;
region | laptop_pct | monitor_pct
-------|------------|------------
East   | 82.8       | 17.2
North  | 77.8       | 22.2
West   | 90.1       | 9.9

Dynamic pivot: when you don't know the column values

Every query above hard-codes the column list. When the values change (new months, new products), you have two options: build the SQL text from the data and run it, or pivot in the tool that displays the results. Building SQL from data is called a dynamic pivot.

SQL Server dynamic PIVOT

Collect the distinct values into a bracketed list with STRING_AGG (SQL Server 2017+), paste it into the query text, and run it with sp_executesql:

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);

SELECT @cols = STRING_AGG(QUOTENAME(quarter), ', ') WITHIN GROUP (ORDER BY quarter)
FROM (SELECT DISTINCT quarter FROM sales) AS q;

SET @sql = N'SELECT region, ' + @cols + N'
FROM (SELECT region, quarter, amount FROM sales) AS src
PIVOT (SUM(amount) FOR quarter IN (' + @cols + N')) AS p
ORDER BY region;';

EXEC sp_executesql @sql;

QUOTENAME wraps each value in square brackets and escapes any ] inside it. That matters: the values come from your data, so without it a value like Q1]; DROP TABLE sales;-- would become part of your SQL. This is the same SQL injection risk as building any query from strings.

PostgreSQL dynamic pivot

Generate the query with string_agg and format(). %L quotes a value as a literal and %I quotes it as an identifier, which handles escaping for you:

SELECT 'SELECT region, '
    || string_agg(format('SUM(amount) FILTER (WHERE quarter = %L) AS %I',
                         quarter, lower(quarter)), ', ' ORDER BY quarter)
    || ' FROM sales GROUP BY region ORDER BY region' AS generated_sql
FROM (SELECT DISTINCT quarter FROM sales) d;
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 FROM sales GROUP BY region ORDER BY region

In psql, end the statement with \gexec instead of a semicolon to run the generated query straight away. In application code, run the generated string as a second query. The same two-step approach works in MySQL with GROUP_CONCAT and a prepared statement.

Often the simplest answer: don't pivot in SQL

If the result is going into a spreadsheet, a BI tool or a pandas DataFrame, return the long GROUP BY region, quarter result and let that tool pivot it. Excel pivot tables, pandas.pivot_table and every dashboard tool handle unknown columns natively, and your SQL stays simple.

SQL UNPIVOT: turn columns into rows

Unpivoting goes the other way. It is useful when you import a spreadsheet with one column per month and need it in a normal long table. Take this wide table:

CREATE TABLE quarterly (region TEXT, q1 INTEGER, q2 INTEGER, q3 INTEGER, q4 INTEGER);
INSERT INTO quarterly VALUES
 ('East',  1500,  900, 1500,  450),
 ('North', NULL,  200,  700, NULL),
 ('West',   800, 1450, NULL, 1300);

UNPIVOT operator (SQL Server, Oracle, Snowflake, DuckDB)

SELECT region, quarter, amount
FROM quarterly
UNPIVOT (amount FOR quarter IN (q1, q2, q3, q4)) AS u
ORDER BY region, quarter;
region | quarter | amount
-------|---------|-------
East   | q1      | 1500
East   | q2      | 900
East   | q3      | 1500
East   | q4      | 450
North  | q2      | 200
North  | q3      | 700
West   | q1      | 800
West   | q2      | 1450
West   | q4      | 1300

Two things to know. First, the new quarter column holds the column names (q1, q2...), so wrap it in UPPER() if you want Q1. Second, UNPIVOT drops NULLs by default: North's empty Q1 and Q4 are gone. Oracle and Snowflake let you keep them with UNPIVOT INCLUDE NULLS. In SQL Server, all the unpivoted columns must also have the same data type, so you may need to CAST them in a subquery first.

UNION ALL (works in every database)

Without an UNPIVOT keyword, write one SELECT per column and stack them with UNION ALL:

SELECT region, 'Q1' AS quarter, q1 AS amount FROM quarterly
UNION ALL SELECT region, 'Q2', q2 FROM quarterly
UNION ALL SELECT region, 'Q3', q3 FROM quarterly
UNION ALL SELECT region, 'Q4', q4 FROM quarterly
ORDER BY region, quarter;
region | quarter | amount
-------|---------|-------
East   | Q1      | 1500
East   | Q2      | 900
East   | Q3      | 1500
East   | Q4      | 450
North  | Q1      | NULL
North  | Q2      | 200
North  | Q3      | 700
North  | Q4      | NULL
West   | Q1      | 800
West   | Q2      | 1450
West   | Q3      | NULL
West   | Q4      | 1300

Unlike the UNPIVOT operator, this keeps the NULL rows. Wrap it in a subquery and add WHERE amount IS NOT NULL if you want them gone. It reads the table once per column, which is fine for a few columns but slow for dozens on a big table.

PostgreSQL: CROSS JOIN LATERAL with VALUES

PostgreSQL can unpivot in a single pass over the table by joining each row to a small VALUES list built from its own columns:

SELECT q.region, v.quarter, v.amount
FROM quarterly q
CROSS JOIN LATERAL (
  VALUES ('Q1', q.q1), ('Q2', q.q2), ('Q3', q.q3), ('Q4', q.q4)
) AS v(quarter, amount)
WHERE v.amount IS NOT NULL
ORDER BY q.region, v.quarter;
 region | quarter | amount
--------+---------+--------
 East   | Q1      |   1500
 East   | Q2      |    900
 East   | Q3      |   1500
 East   | Q4      |    450
 North  | Q2      |    200
 North  | Q3      |    700
 West   | Q1      |    800
 West   | Q2      |   1450
 West   | Q4      |   1300

SQL Server has the same trick with CROSS APPLY (VALUES ...), and many people prefer it to UNPIVOT because it keeps NULLs unless you filter them and handles mixed data types more easily.

Pivot syntax by database

DatabasePivotUnpivotCASE pivotFILTER
SQL ServerPIVOT (... FOR c IN ([a],[b]))UNPIVOT, CROSS APPLY (VALUES ...)YesNo
OraclePIVOT (... FOR c IN ('a' AS a)) (11g+)UNPIVOT [INCLUDE NULLS]YesNo
PostgreSQLcrosstab() (tablefunc)CROSS JOIN LATERAL (VALUES ...)YesYes (9.4+)
MySQL / MariaDBNoneUNION ALLYes (or IF())No
SQLiteNoneUNION ALLYesYes (3.30+)
SnowflakePIVOT, incl. IN (ANY)UNPIVOT [INCLUDE NULLS]YesNo
Databricks / Spark SQLPIVOTUNPIVOTYesYes
DuckDBPIVOT ... ON ... USING and Oracle formUNPIVOTYesYes
BigQueryPIVOT (... FOR c IN ('a'))UNPIVOTYesNo

Common mistakes

  1. Feeding extra columns into PIVOT. Every leftover column becomes a grouping column, so you get one row per id. Select only the three columns you need in a subquery first.
  2. Forgetting the aggregate in a CASE pivot. SELECT region, CASE WHEN quarter = 'Q1' THEN amount END AS q1 ... GROUP BY region fails with "must appear in the GROUP BY clause" in PostgreSQL and SQL Server, and returns an arbitrary row in SQLite and older MySQL.
  3. Grouping by the pivot column. GROUP BY region, quarter gives you one row per region and quarter again, with the CASE columns mostly empty. Group only by the row heading.
  4. Using COUNT with ELSE 0. COUNT counts zeros, so every cell equals the group size. Drop the ELSE or use SUM.
  5. Using ELSE 0 with AVG or MIN. The zeros become real values and skew the answer. Leave the ELSE out.
  6. Using one-argument crosstab(). Values shift into the wrong columns when a row has gaps. Always pass the second query with the column list.
  7. Value case and spelling. 'q1' is not 'Q1' in most databases, and a typo gives you an empty column with no error. Check the distinct values first with SELECT DISTINCT.
  8. Building dynamic pivots without quoting. Use QUOTENAME in SQL Server or format('%I') in PostgreSQL so data values cannot inject SQL.

FAQ

What is PIVOT in SQL?

PIVOT rotates a result so that the distinct values of one column become separate columns, with an aggregate such as SUM in each cell. It turns rows into columns. You can do it with the PIVOT keyword in SQL Server, Oracle and Snowflake, or with GROUP BY and CASE in any database.

How do I convert rows to columns in SQL?

Group by the column you want as rows and add one SUM(CASE WHEN col = 'value' THEN amount END) AS value expression per new column. See the CASE pivot. In SQL Server you can use PIVOT instead.

Does MySQL have a PIVOT function?

No. MySQL and MariaDB have no PIVOT keyword. Use conditional aggregation with CASE or IF(), as shown in the MySQL section.

Does PostgreSQL support PIVOT?

Not as a keyword. Use SUM(...) FILTER (WHERE ...) or CASE inside an aggregate, or the crosstab() function from the tablefunc extension.

Can you pivot without an aggregate function?

Not really: SQL has to combine every row that lands in a cell into one value. When each cell holds exactly one row, use MAX (or MIN), which simply returns that value and also works on text and dates.

How do I pivot on text values instead of numbers?

Use MAX(CASE WHEN attribute = 'color' THEN value END) AS color. This is the standard way to flatten key-value (EAV) tables into one row per entity.

What is the difference between PIVOT and UNPIVOT?

PIVOT turns row values into columns (long to wide). UNPIVOT turns columns into rows (wide to long). UNPIVOT cannot always undo a PIVOT exactly, because PIVOT aggregates several rows into one cell.

Can I pivot without knowing the column names?

Not in a single static query. You either generate the SQL from the distinct values and execute it (a dynamic pivot), use Snowflake's IN (ANY) or DuckDB's automatic PIVOT ... ON, or return long data and pivot in Excel, pandas or your BI tool.

Is PIVOT faster than CASE?

In SQL Server the two usually compile to very similar plans, so performance is about the same. Choose whichever is easier to read. CASE is more flexible (multiple aggregates, different conditions per column) and portable to every database.

Practice

A pivot is two skills you can practise separately. The free interactive GROUP BY lesson and CASE lesson both run in your browser with no signup. Once those click, the SQL practice problems give you GROUP BY reps, and the pivot questions in SQL interview questions for data analysts (question 12) and SQL coding interview questions (problem 11) are exactly this technique. Keep the SQL cheat sheet open for the syntax, and see SQL window functions for the other big tool in reporting queries.

Further reading: Microsoft's Using PIVOT and UNPIVOT reference and the PostgreSQL tablefunc (crosstab) documentation.