Lesson 12.9 · Reading time: ~7 min

A pivot turns the values of one column into separate columns: instead of rows "category, rating, count", you get one row per category with a column for each rating, like a spreadsheet pivot table. Pivots are the base of most cross-tab reports, and "rows to columns" is a common interview task. In this lesson you'll pivot with conditional aggregation, which works in every database, build a dynamic pivot in MySQL, see the PIVOT operator of SQL Server, and unpivot columns back into rows.

SQL Pivot: Rows to Columns

GROUP BY category, rating gives the right numbers in an inconvenient shape: 80 rows of category–rating pairs. People read a matrix more easily: categories down, ratings across. MySQL, MariaDB, PostgreSQL and SQLite have no PIVOT keyword, and they don't need one: each output column is an aggregate with a condition. This is conditional aggregation from Lesson 4.4, applied to reshape the result.

The examples run on the Sakila sample database (MySQL 8.0).

Pivot with Conditional Aggregation

Group by the row label (category), and for each future column, count only the rows with that rating:

SELECT
    c.name AS category,
    SUM(CASE WHEN f.rating = 'G' THEN 1 ELSE 0 END) AS g,
    SUM(CASE WHEN f.rating = 'PG' THEN 1 ELSE 0 END) AS pg,
    SUM(CASE WHEN f.rating = 'PG-13' THEN 1 ELSE 0 END) AS pg_13,
    SUM(CASE WHEN f.rating = 'R' THEN 1 ELSE 0 END) AS r,
    SUM(CASE WHEN f.rating = 'NC-17' THEN 1 ELSE 0 END) AS nc_17,
    COUNT(*) AS total
FROM film f
JOIN film_category fc ON fc.film_id = f.film_id
JOIN category c ON c.category_id = fc.category_id
GROUP BY c.name
ORDER BY c.name;

Result: 16 rows, one per category, with the number of films for each rating and the total.

How it works:

• GROUP BY c.name defines the rows of the pivot.
• Each CASE turns a film into 1 if it belongs to that column and 0 if not, so SUM counts the films of one rating.
• To pivot amounts instead of counts, put the value into CASE: SUM(CASE WHEN ... THEN amount ELSE 0 END).

In MySQL, a comparison returns 1 or 0, so SUM(f.rating = 'G') is a shorter equivalent. PostgreSQL offers COUNT(*) FILTER (WHERE f.rating = 'G').

Pivot by Month

A frequent business report: revenue by store, with a column per month. The values are amounts, and the condition is the month:

SELECT
    s.store_id,
    SUM(CASE WHEN MONTH(p.payment_date) = 5 THEN p.amount ELSE 0 END) AS may_2005,
    SUM(CASE WHEN MONTH(p.payment_date) = 6 THEN p.amount ELSE 0 END) AS jun_2005,
    SUM(CASE WHEN MONTH(p.payment_date) = 7 THEN p.amount ELSE 0 END) AS jul_2005,
    SUM(CASE WHEN MONTH(p.payment_date) = 8 THEN p.amount ELSE 0 END) AS aug_2005
FROM payment p
JOIN staff s ON s.staff_id = p.staff_id
WHERE p.payment_date >= '2005-01-01'
  AND p.payment_date < '2006-01-01'
GROUP BY s.store_id
ORDER BY s.store_id;

Result: 2 rows; for example, store 1 earned 2621.83 in May and 14003.54 in July 2005.

The WHERE condition limits the data to one year, because MONTH() alone would mix July 2005 with July 2006. Use ELSE 0 when an empty cell should show 0; without ELSE, SUM returns NULL for it.

Dynamic Pivot in MySQL

Every column of a query must be known before it runs. If the list of pivot values changes (new ratings, new months), the query has to be generated. In MySQL, build the column list with GROUP_CONCAT and run the text as a prepared statement:

SET @columns = (
    SELECT GROUP_CONCAT(DISTINCT
        CONCAT('SUM(f.rating = ''', rating, ''') AS `', rating, '`')
        ORDER BY rating)
    FROM film
);

SET @sql = CONCAT(
    'SELECT c.name AS category, ', @columns, '
     FROM film f
     JOIN film_category fc ON fc.film_id = f.film_id
     JOIN category c ON c.category_id = fc.category_id
     GROUP BY c.name
     ORDER BY c.name');

PREPARE pivot_stmt FROM @sql;
EXECUTE pivot_stmt;
DEALLOCATE PREPARE pivot_stmt;

Result: the same 16 categories with one column per rating; the columns are built from the data.

Two cautions: the values are inserted into SQL text, so use this only with values from your own tables, never with user input; and very long column lists may need a larger group_concat_max_len (1024 characters by default). Often it's simpler to return the long GROUP BY result and pivot it in the application or BI tool.

The PIVOT Operator in SQL Server and Oracle

SQL Server and Oracle have a dedicated syntax. The same report in SQL Server:

SELECT category, [G], [PG], [PG-13], [R], [NC-17]
FROM (
    SELECT c.name AS category, f.rating, f.film_id
    FROM film f
    JOIN film_category fc ON fc.film_id = f.film_id
    JOIN category c ON c.category_id = fc.category_id
) AS src
PIVOT (
    COUNT(film_id) FOR rating IN ([G], [PG], [PG-13], [R], [NC-17])
) AS pvt;

The values in IN (...) must still be listed in advance. Conditional aggregation does the same and works everywhere, so it's the safer answer in an interview.

Unpivot: Columns Back to Rows

The reverse task appears when data arrives as a spreadsheet: one column per month. Unpivot it with one SELECT per column joined by UNION ALL:

WITH store_months AS (
    SELECT
        s.store_id,
        SUM(CASE WHEN MONTH(p.payment_date) = 7 THEN p.amount ELSE 0 END) AS jul_2005,
        SUM(CASE WHEN MONTH(p.payment_date) = 8 THEN p.amount ELSE 0 END) AS aug_2005
    FROM payment p
    JOIN staff s ON s.staff_id = p.staff_id
    WHERE p.payment_date >= '2005-07-01'
      AND p.payment_date < '2005-09-01'
    GROUP BY s.store_id
)
SELECT store_id, '2005-07' AS month, jul_2005 AS amount FROM store_months
UNION ALL
SELECT store_id, '2005-08', aug_2005 FROM store_months
ORDER BY store_id, month;

Result: 4 rows, one per store and month, for example store 1 – 2005-07 – 14003.54.

SQL Server and Oracle also have an UNPIVOT operator; PostgreSQL can use CROSS JOIN LATERAL (VALUES ...).

Practice

• Population Distribution (Pivot)
• Film Distribution by Category and Store
• Revenue by Month and Payment Type

Key Takeaways:

• A pivot turns the values of one column into separate columns.
• GROUP BY sets the rows; SUM(CASE WHEN column = value THEN ... ELSE 0 END) creates each column. It works in every database.
• For amounts, put the value in THEN; for counts, put 1.
• When the columns aren't known in advance, generate the query: in MySQL with GROUP_CONCAT and PREPARE.
• SQL Server and Oracle have PIVOT and UNPIVOT; UNION ALL unpivots anywhere.

Frequently Asked Questions

How do I pivot rows into columns in MySQL?

MySQL has no PIVOT operator. Use conditional aggregation: GROUP BY the row label and add one SUM(CASE WHEN pivot_column = 'value' THEN amount ELSE 0 END) AS value per output column.

How do I create a pivot with dynamic columns?

Generate the SQL text: collect the distinct values with GROUP_CONCAT, build the SUM(CASE ...) list from them, then PREPARE and EXECUTE the statement. In SQL Server, the same is done with STRING_AGG and sp_executesql. Use only trusted values from your own tables.

What is the difference between PIVOT and conditional aggregation?

They give the same result. PIVOT is a shorter syntax available only in SQL Server and Oracle. Conditional aggregation works in every database and allows different conditions and aggregates for each column.

How do I unpivot columns into rows?

Write one SELECT per column, each returning the key, the column name as a literal and the column value, and combine them with UNION ALL. SQL Server and Oracle also have UNPIVOT.

→ More tasks on the Sakila database