Урок 12.9 · Время чтения: ~7 мин

Pivot превращает значения одного столбца в отдельные столбцы: вместо строк «категория, рейтинг, количество» получается одна строка на категорию со столбцом для каждого рейтинга, как в сводной таблице Excel. Pivot лежит в основе большинства перекрёстных отчётов, а «строки в столбцы» — частая задача на собеседовании. В этом уроке вы сделаете pivot через условную агрегацию, которая работает в любой СУБД, построите динамический pivot в MySQL, увидите оператор PIVOT в SQL Server и развернёте столбцы обратно в строки.

Pivot в SQL: строки в столбцы

GROUP BY category, rating даёт правильные числа в неудобной форме: 80 строк с парами «категория — рейтинг». Человеку проще читать матрицу: категории по вертикали, рейтинги по горизонтали. В MySQL, MariaDB, PostgreSQL и SQLite нет ключевого слова PIVOT, и оно не нужно: каждый столбец результата — это агрегат с условием. Это условная агрегация из урока 4.4, применённая для изменения формы результата.

Примеры работают на учебной базе Sakila (MySQL 8.0).

Pivot через условную агрегацию

Сгруппируйте по подписи строки (категории), а для каждого будущего столбца считайте только строки с нужным рейтингом:

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;

Результат: 16 строк, по одной на категорию, с числом фильмов каждого рейтинга и общим итогом.

Как это работает:

• GROUP BY c.name задаёт строки сводной таблицы.
• Каждый CASE превращает фильм в 1, если он относится к этому столбцу, и в 0, если нет, поэтому SUM считает фильмы одного рейтинга.
• Чтобы разворачивать суммы, а не количества, поставьте значение в CASE: SUM(CASE WHEN ... THEN amount ELSE 0 END).

В MySQL сравнение возвращает 1 или 0, поэтому SUM(f.rating = 'G') — более короткая запись того же самого. В PostgreSQL есть COUNT(*) FILTER (WHERE f.rating = 'G').

Pivot по месяцам

Частый бизнес-отчёт: выручка по магазинам со столбцом на каждый месяц. Значения — суммы, а условие — месяц:

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;

Результат: 2 строки; например, магазин 1 заработал 2621.83 в мае и 14003.54 в июле 2005 года.

Условие WHERE ограничивает данные одним годом, потому что один MONTH() смешал бы июль 2005 и июль 2006 года. Используйте ELSE 0, когда пустая ячейка должна показывать 0; без ELSE SUM вернёт для неё NULL.

Динамический pivot в MySQL

Все столбцы запроса должны быть известны до его выполнения. Если список значений для разворота меняется (новые рейтинги, новые месяцы), запрос нужно генерировать. В MySQL соберите список столбцов через GROUP_CONCAT и выполните текст как подготовленный запрос:

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;

Результат: те же 16 категорий со столбцом на каждый рейтинг; столбцы построены по данным.

Две оговорки: значения вставляются в текст SQL, поэтому используйте этот приём только со значениями из своих таблиц и никогда — с пользовательским вводом; а очень длинным спискам столбцов может понадобиться увеличить group_concat_max_len (по умолчанию 1024 символа). Часто проще вернуть длинный результат GROUP BY и развернуть его в приложении или BI-инструменте.

Оператор PIVOT в SQL Server и Oracle

В SQL Server и Oracle есть отдельный синтаксис. Тот же отчёт в 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;

Значения в IN (...) всё равно нужно перечислить заранее. Условная агрегация делает то же самое и работает везде, поэтому на собеседовании это более надёжный ответ.

Unpivot: столбцы обратно в строки

Обратная задача возникает, когда данные приходят в виде таблицы Excel: по столбцу на месяц. Разверните её одним SELECT на каждый столбец, объединив их через 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;

Результат: 4 строки, по одной на магазин и месяц, например магазин 1 — 2005-07 — 14003.54.

В SQL Server и Oracle есть также оператор UNPIVOT; в PostgreSQL можно использовать CROSS JOIN LATERAL (VALUES ...).

Практика

• Распределение населения (pivot)
• Распределение фильмов по категориям и магазинам
• Выручка по месяцам и способам оплаты

Ключевые выводы:

• Pivot превращает значения одного столбца в отдельные столбцы.
• GROUP BY задаёт строки; SUM(CASE WHEN column = value THEN ... ELSE 0 END) создаёт каждый столбец. Это работает в любой СУБД.
• Для сумм ставьте значение в THEN, для количества — 1.
• Если столбцы заранее неизвестны, генерируйте запрос: в MySQL — через GROUP_CONCAT и PREPARE.
• В SQL Server и Oracle есть PIVOT и UNPIVOT; UNION ALL разворачивает столбцы в строки где угодно.

Часто задаваемые вопросы

Как превратить строки в столбцы в MySQL?

В MySQL нет оператора PIVOT. Используйте условную агрегацию: GROUP BY по подписи строки и по одному SUM(CASE WHEN pivot_column = 'value' THEN amount ELSE 0 END) AS value на каждый столбец результата.

Как сделать pivot с динамическими столбцами?

Сгенерируйте текст SQL: соберите различные значения через GROUP_CONCAT, постройте из них список SUM(CASE ...), затем выполните запрос через PREPARE и EXECUTE. В SQL Server то же самое делают с помощью STRING_AGG и sp_executesql. Используйте только надёжные значения из своих таблиц.

Чем PIVOT отличается от условной агрегации?

Результат одинаковый. PIVOT — более короткий синтаксис, доступный только в SQL Server и Oracle. Условная агрегация работает в любой СУБД и позволяет задавать разные условия и агрегаты для каждого столбца.

Как развернуть столбцы обратно в строки (unpivot)?

Напишите по одному SELECT на каждый столбец; каждый возвращает ключ, имя столбца в виде литерала и значение столбца. Объедините их через UNION ALL. В SQL Server и Oracle есть также UNPIVOT.

→ Другие задачи на базе данных Sakila