Пивот в SQL: что это и как развернуть строки в столбцы

PIVOT в SQL: что это и как превратить строки в столбцы

PIVOT поворачивает таблицу: значения одного столбца становятся заголовками, а данные под ними суммируются.

На одной таблице продаж показываем, как сделать это оператором PIVOT в SQL Server и Oracle и через CASE, FILTER и crosstab в PostgreSQL и MySQL, как развернуть отчёт обратно и какие пять ошибок ломают результат.

Обложка: PIVOT в SQL: что это и как превратить строки в столбцы

Pivot (пивот) переводится как «поворот». В SQL так называют разворот таблицы: значения одного столбца становятся заголовками новых столбцов, а данные под ними сводятся агрегатной функцией, например SUM или COUNT. В бизнесе пивотом называют смену стратегии стартапа, но здесь речь только про базы данных.

Отдельный оператор PIVOT есть в SQL Server и Oracle. В PostgreSQL и MySQL его нет, и ту же сводную таблицу собирают через CASE внутри SUM и GROUP BY. Если вы только начинаете, сначала загляните в обзор языка SQL, а ниже разберём все способы на одной таблице, с запросами и выводом.

CheckroiПодборка курсов по SQL420 курсов • 40 школСравните цены, школы, программу и найдите выгодные предложения по обучениюСравнить→

Что делает PIVOT: строки в столбцы на примере

Рой разворачивает таблицу продаж в сводный отчёт

Возьмём таблицу продаж в «длинном» формате: каждая сделка лежит отдельной строкой, у менеджера может быть несколько сделок за месяц.

CREATE TABLE sales (
    manager TEXT,
    month   TEXT,
    amount  INT
);

INSERT INTO sales VALUES
    ('Анна',  'янв', 120),
    ('Анна',  'янв',  80),
    ('Анна',  'фев', 150),
    ('Анна',  'мар',  90),
    ('Борис', 'янв',  60),
    ('Борис', 'мар', 200),
    ('Вера',  'фев', 110),
    ('Вера',  'фев',  40),
    ('Вера',  'мар', 130);

Руководителю такая таблица неудобна. Ему нужен отчёт, где менеджеры идут строками, месяцы столбцами, а в ячейках стоит сумма продаж. Это и есть поворот:

manager	янв	фев	мар
Анна	200	150	90
Борис	60	NULL	200
Вера	NULL	150	130

Обратите внимание на три вещи:

Девять строк превратились в три: две январские сделки Анны (120 и 80) сложились в 200.
Значения столбца month стали названиями столбцов.
Где сделок не было, стоит NULL, а не ноль.

Если вы работали со сводными таблицами в Excel, идея та же: поле с месяцем перетащили в столбцы, сумму в значения. А вот транспонирование к пивоту отношения не имеет: оно просто меняет строки и столбцы местами и ничего не суммирует.

Универсальный способ: CASE и GROUP BY

Этот запрос работает в PostgreSQL, MySQL, SQLite, SQL Server и Oracle, потому что опирается только на стандартные CASE, SUM и GROUP BY. Его ещё называют условной агрегацией.

SELECT
    manager,
    SUM(CASE WHEN month = 'янв' THEN amount END) AS янв,
    SUM(CASE WHEN month = 'фев' THEN amount END) AS фев,
    SUM(CASE WHEN month = 'мар' THEN amount END) AS мар
FROM sales
GROUP BY manager
ORDER BY manager;

Вывод в PostgreSQL:

 manager | янв | фев | мар
---------+-----+-----+-----
 Анна    | 200 | 150 |  90
 Борис   |  60 |     | 200
 Вера    |     | 150 | 130
(3 rows)

Как это устроено:

GROUP BY manager оставляет по одной строке на менеджера.
Каждый CASE пропускает в сумму только строки своего месяца, для остальных возвращает NULL.
SUM складывает то, что прошло, и пропускает NULL.

Пустые ячейки здесь потому, что у CASE нет ветки ELSE. Если в отчёте нужен ноль, добавьте ELSE 0. В тот же запрос можно добавить итог по строке:

SELECT
    manager,
    SUM(CASE WHEN month = 'янв' THEN amount ELSE 0 END) AS янв,
    SUM(CASE WHEN month = 'фев' THEN amount ELSE 0 END) AS фев,
    SUM(CASE WHEN month = 'мар' THEN amount ELSE 0 END) AS мар,
    SUM(amount) AS итого
FROM sales
GROUP BY manager
ORDER BY manager;

 manager | янв | фев | мар | итого
---------+-----+-----+-----+-------
 Анна    | 200 | 150 |  90 |   440
 Борис   |  60 |   0 | 200 |   260
 Вера    |   0 | 150 | 130 |   280
(3 rows)

Ноль можно получить и иначе: обернуть сумму в COALESCE. Чем NULL отличается от нуля и пустой строки, подробно разобрано в статье про NULL в SQL. Вместо суммы годится любой агрегат: COUNT посчитает число сделок, MAX покажет самую крупную.

Канал основателя Checkroi Вани Буявца3 800 человек читают мой Телеграм-канал про нейросетиСобрал промпты для Claude Code и ChatGPT, разборы ИИ-инструментов и лайфхаки по продвижению бизнеса в одном местеПрисоединиться

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

В T-SQL поворот записывается отдельным оператором после FROM. Синтаксис из документации Microsoft, переложенный на нашу таблицу:

SELECT manager, [янв], [фев], [мар]
FROM (
    SELECT manager, month, amount
    FROM sales
) AS src
PIVOT (
    SUM(amount)
    FOR month IN ([янв], [фев], [мар])
) AS pvt
ORDER BY manager;

Разбор по частям:

SUM(amount) задаёт агрегатную функцию и столбец, который сводим.
FOR month называет столбец, чьи значения станут заголовками.
IN ([янв], [фев], [мар]) перечисляет эти значения. Пишут их как имена столбцов, в квадратных скобках, а не как строки в кавычках.
AS pvt даёт имя результату поворота.

Подзапрос src здесь не для красоты. PIVOT сам группирует строки по всем столбцам источника, кроме сводимого и того, что после FOR. Поэтому в подзапрос берут только нужные три столбца: про лишний столбец вроде id речь пойдёт в разделе об ошибках.

Результат совпадёт с вариантом на CASE: у Бориса в феврале и у Веры в январе будет NULL. Microsoft отдельно предупреждает, что агрегатные функции внутри PIVOT пропускают NULL в столбце значений. Если вы работаете с этой СУБД каждый день, посмотрите курсы по Microsoft SQL Server.

Поворот таблицы в Oracle

В Oracle конструкция почти та же, только значения в IN пишутся как строковые литералы, и каждому можно дать псевдоним столбца:

SELECT *
FROM (
    SELECT manager, month, amount
    FROM sales
)
PIVOT (
    SUM(amount)
    FOR month IN ('янв' AS jan, 'фев' AS feb, 'мар' AS mar)
)
ORDER BY manager

Псевдонимы jan, feb, mar здесь не обязательны, но без них, по справочнику Oracle, заголовок столбца станет идентификатором в кавычках, и обращаться к нему придётся так же, в кавычках. Там же сказано, что список значений можно взять подзапросом или словом ANY, но только вместе с ключевым словом XML. Тогда вместо отдельных столбцов придёт один столбец с XML-строкой, то есть таблицы в привычном виде не будет.

Сводная таблица в PostgreSQL

Оператора PIVOT в PostgreSQL нет, зато есть три замены. Все примеры ниже можно повторить в любом клиенте, например в DBeaver или в psql, а таблицу из начала статьи создать и через pgAdmin.

Агрегат с FILTER

Это тот же CASE, только короче. По документации PostgreSQL, с FILTER в агрегат попадают только строки, для которых условие истинно:

SELECT
    manager,
    SUM(amount) FILTER (WHERE month = 'янв') AS янв,
    SUM(amount) FILTER (WHERE month = 'фев') AS фев,
    SUM(amount) FILTER (WHERE month = 'мар') AS мар,
    COUNT(*)    FILTER (WHERE amount >= 100) AS крупных_сделок
FROM sales
GROUP BY manager
ORDER BY manager;

 manager | янв | фев | мар | крупных_сделок
---------+-----+-----+-----+----------------
 Анна    | 200 | 150 |  90 |              2
 Борис   |  60 |     | 200 |              1
 Вера    |     | 150 | 130 |              2
(3 rows)

Последний столбец показывает, что условие не обязано касаться месяца: в одном запросе можно смешать поворот и любые счётчики на COUNT. Если хотите разобраться с этой СУБД глубже, посмотрите курсы по PostgreSQL.

Функция crosstab из tablefunc

Расширение tablefunc поставляется вместе с PostgreSQL как дополнительный модуль, его включают одной командой. Функции передают два запроса: первый отдаёт тройки «строка, категория, значение», второй перечисляет категории в нужном порядке.

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
    $$ SELECT manager, month, SUM(amount)
       FROM sales
       GROUP BY manager, month
       ORDER BY 1 $$,
    $$ VALUES ('янв'), ('фев'), ('мар') $$
) AS ct(manager TEXT, янв BIGINT, фев BIGINT, мар BIGINT);

CREATE EXTENSION
 manager | янв | фев | мар
---------+-----+-----+-----
 Анна    | 200 | 150 |  90
 Борис   |  60 |     | 200
 Вера    |     | 150 | 130
(3 rows)

Типы в AS ct(...) обязательны: crosstab возвращает набор записей без описания, и структуру результата задаёте вы.

Команда \crosstabview в psql

Если отчёт нужен глазами, а не в приложении, хватит консольного клиента psql. Команда \crosstabview выполняет запрос и сама раскладывает его по сетке, столбцы перечислять не нужно:

SELECT manager, month, SUM(amount)
FROM sales
GROUP BY manager, month
ORDER BY manager
\crosstabview

 manager | мар | янв | фев
---------+-----+-----+-----
 Анна    |  90 | 200 | 150
 Борис   | 200 |  60 |
 Вера    | 130 |     | 150
(3 rows)

Столбцы встали в порядке, в каком месяцы впервые попались в результате. Задать порядок можно четвёртым аргументом команды, sortcolH. И помните, что это функция клиента: из Python или BI-системы такой запрос не вызвать.

Строки в столбцы в MySQL

В MySQL нет ни PIVOT, ни FILTER. Попытка записать агрегат с FILTER заканчивается синтаксической ошибкой:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(WHERE month = 'янв') AS янв
FROM sales GROUP BY manager' at line 1

Поэтому в MySQL пишут CASE из раздела выше, он работает без изменений, как и обычный SELECT в MySQL. А когда месяцы заранее неизвестны, запрос собирают динамически: подзапрос с DISTINCT находит все месяцы, GROUP_CONCAT склеивает CASE для каждого, PREPARE выполняет получившуюся строку.

SET @cols = (
    SELECT GROUP_CONCAT(
        CONCAT('SUM(CASE WHEN month = ''', month,
               ''' THEN amount END) AS `', month, '`')
        ORDER BY FIELD(month, 'янв', 'фев', 'мар', 'апр')
    )
    FROM (SELECT DISTINCT month FROM sales) AS m
);

SET @sql = CONCAT('SELECT manager, ', @cols,
                  ' FROM sales GROUP BY manager ORDER BY manager');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

+------------+--------+--------+--------+
| manager    | янв    | фев    | мар    |
+------------+--------+--------+--------+
| Анна       |    200 |    150 |     90 |
| Борис      |     60 |   NULL |    200 |
| Вера       |   NULL |    150 |    130 |
+------------+--------+--------+--------+

Проверим, что столбцы появляются сами. Добавим апрельскую сделку и запустим тот же скрипт без правок:

INSERT INTO sales VALUES ('Борис', 'апр', 70);

+------------+--------+--------+--------+--------+
| manager    | янв    | фев    | мар    | апр    |
+------------+--------+--------+--------+--------+
| Анна       |    200 |    150 |     90 |   NULL |
| Борис      |     60 |   NULL |    200 |     70 |
| Вера       |   NULL |    150 |    130 |   NULL |
+------------+--------+--------+--------+--------+

FIELD внутри GROUP_CONCAT задаёт календарный порядок столбцов, иначе он не гарантирован. Названия месяцев попадают прямо в текст запроса, поэтому такой приём годится только для значений из вашей же таблицы, а не для того, что ввёл пользователь.

Канал основателя Checkroi Вани БуявцаЗабирайте промпты и обучение по нейросетям в моём Телеграм-каналеБольше 3 800 человек уже применяют Claude Code, ChatGPT и другие нейросети в работе, учёбе, бизнесе и жизниПерейти в канал

Обратная операция: UNPIVOT

UNPIVOT делает наоборот: превращает столбцы в строки. Представим, что отчёт выше сохранили в таблицу report со столбцами manager, янв, фев, мар. В SQL Server его разворачивают обратно так:

SELECT manager, month, amount
FROM report
UNPIVOT (
    amount FOR month IN ([янв], [фев], [мар])
) AS unpvt;

UNPIVOT не восстанавливает исходную таблицу. В документации Microsoft сказано, что UNPIVOT не является точным обратным PIVOT: две январские сделки Анны уже слились в 200, а строки с NULL из результата пропадают. В Oracle это поведение настраивается: по справочнику, INCLUDE NULLS оставляет такие строки, а по умолчанию они отбрасываются.

В PostgreSQL отдельного UNPIVOT нет, его заменяет соединение с VALUES:

CREATE TABLE report AS
SELECT
    manager,
    SUM(amount) FILTER (WHERE month = 'янв') AS янв,
    SUM(amount) FILTER (WHERE month = 'фев') AS фев,
    SUM(amount) FILTER (WHERE month = 'мар') AS мар
FROM sales
GROUP BY manager;

SELECT r.manager, v.month, v.amount
FROM report AS r
CROSS JOIN LATERAL (
    VALUES (1, 'янв', r.янв), (2, 'фев', r.фев), (3, 'мар', r.мар)
) AS v(n, month, amount)
WHERE v.amount IS NOT NULL
ORDER BY r.manager, v.n;

 manager | month | amount
---------+-------+--------
 Анна    | янв   |    200
 Анна    | фев   |    150
 Анна    | мар   |     90
 Борис   | янв   |     60
 Борис   | мар   |    200
 Вера    | фев   |    150
 Вера    | мар   |    130
(7 rows)

В MySQL то же самое делают через UNION ALL, по одному SELECT на столбец. FIELD в ORDER BY возвращает месяцы в календарном порядке:

CREATE TABLE report AS
SELECT
    manager,
    SUM(CASE WHEN month = 'янв' THEN amount END) AS янв,
    SUM(CASE WHEN month = 'фев' THEN amount END) AS фев,
    SUM(CASE WHEN month = 'мар' THEN amount END) AS мар
FROM sales
GROUP BY manager;

SELECT manager, 'янв' AS month, янв AS amount FROM report WHERE янв IS NOT NULL
UNION ALL
SELECT manager, 'фев', фев FROM report WHERE фев IS NOT NULL
UNION ALL
SELECT manager, 'мар', мар FROM report WHERE мар IS NOT NULL
ORDER BY manager, FIELD(month, 'янв', 'фев', 'мар');

+------------+--------+--------+
| manager    | month  | amount |
+------------+--------+--------+
| Анна       | янв    |    200 |
| Анна       | фев    |    150 |
| Анна       | мар    |     90 |
| Борис      | янв    |     60 |
| Борис      | мар    |    200 |
| Вера       | фев    |    150 |
| Вера       | мар    |    130 |
+------------+--------+--------+

Какой способ выбрать

Рой доволен: отчёт по менеджерам собран одним запросом

СУБД	Поворот	Если столбцы заранее неизвестны	Обратный разворот
SQL Server	PIVOT или CASE	динамический SQL: собрать строку запроса и выполнить	UNPIVOT
Oracle	PIVOT или CASE	PIVOT XML с подзапросом или ANY, результат в XML	UNPIVOT
PostgreSQL	FILTER, CASE или crosstab	\crosstabview в psql либо динамический SQL	CROSS JOIN LATERAL с VALUES
MySQL	CASE	GROUP_CONCAT и PREPARE	UNION ALL

Если запрос должен переезжать между базами, берите CASE: он одинаков везде. Когда отчёт строится поверх уже посчитанных данных, например с итогами по оконным функциям, поворот ставят последним шагом, во внешнем запросе: сначала окно считает итоги по исходным строкам, потом результат разворачивают.

Частые ошибки и что с ними делать

Ноутбук с SQL-запросом, распечатки таблиц и стикеры

Строк в результате больше, чем ожидали. В SQL Server подали в PIVOT всю таблицу через FROM sales, а в ней есть, скажем, id сделки. PIVOT группирует по всем оставшимся столбцам, и каждая сделка становится отдельной строкой. Решение: подзапрос, где выбраны только столбец строк, столбец заголовков и сводимое значение.
Значения crosstab разъехались по чужим столбцам. Так бывает, если вызвать функцию с одним запросом, без списка категорий:
```
SELECT *
FROM crosstab(
    $$ SELECT manager, month, SUM(amount)
       FROM sales
       GROUP BY manager, month
       ORDER BY 1 $$
) AS ct(manager TEXT, янв BIGINT, фев BIGINT, мар BIGINT);
```
```
 manager | янв | фев | мар
---------+-----+-----+-----
 Анна    |  90 | 200 | 150
 Борис   | 200 |  60 |
 Вера    | 150 | 130 |
(3 rows)
```
У Анны январь показывает 90, у Бориса мартовские 200 попали в январь. По документации tablefunc, форма с одним аргументом заполняет столбцы слева направо подряд и на категорию не смотрит. Решение: форма с двумя запросами, как в разделе про PostgreSQL. Порядок категорий во втором запросе должен совпадать с порядком столбцов в AS ct(...).
В отчёте NULL, а нужен ноль. Агрегат по пустому набору строк даёт NULL. Решение: ELSE 0 внутри CASE или COALESCE вокруг агрегата.
Динамический запрос в MySQL обрывается на середине. GROUP_CONCAT обрезает результат по переменной group_concat_max_len, по справочнику MySQL это 1024 по умолчанию. При десятках столбцов строка запроса длиннее, и PREPARE падает с синтаксической ошибкой. Решение: перед сборкой выполнить SET SESSION group_concat_max_len = 100000;.
Новый месяц не появился в отчёте. В PIVOT, FILTER, CASE и crosstab список столбцов зашит в запрос, и новые значения он просто не видит. Решение: динамическая сборка запроса или \crosstabview, если отчёт нужен только в консоли.

Где освоить SQL системно

Без базового SQL запрос с PIVOT приходится собирать по чужим примерам. Группировки и агрегаты спрашивают и на собеседованиях аналитиков. Нужна база: соединения таблиц через JOIN, группировки, подзапросы и оконные функции. Её дают курсы из каталога «SQL», а если вы выбираете профессию, посмотрите, как стать SQL-разработчиком или чем занимается SQL-аналитик.

Курс	Школа	Стоимость со скидкой	В рассрочку	Длительность	Обзор курса от Checkroi
Нейросети для рабочих задач Перейти на сайт курса	Skillbox	31 290 ₽	-	1 месяц	Обзор курса
SQL для анализа данных Перейти на сайт курса	Skillbox	50 865 ₽	6203 ₽/мес.	2 месяца	Обзор курса
SQL-разработчик: тариф PRO Перейти на сайт курса	Эдюсон	89 900 ₽	7491 ₽/мес.	2 месяца	Обзор курса
SQL-разработчик: тариф Базовый Перейти на сайт курса	Эдюсон	79 900 ₽	3329 ₽/мес.	3 месяца	Обзор курса
Data-Driven подход в разработке продукта: SQL и Yandex DataLens Перейти на сайт курса	ProductStar	29 990 ₽	2999 ₽/мес.	1 месяц	Обзор курса

Нейросети для рабочих задач
Skillbox31 290 ₽1 месяц
Перейти на сайт курса Обзор
SQL для анализа данных
Skillbox50 865 ₽6203 ₽/мес2 месяца
Перейти на сайт курса Обзор
SQL-разработчик: тариф PRO
Эдюсон89 900 ₽7491 ₽/мес2 месяца
Перейти на сайт курса Обзор
SQL-разработчик: тариф Базовый
Эдюсон79 900 ₽3329 ₽/мес3 месяца
Перейти на сайт курса Обзор
Data-Driven подход в разработке продукта: SQL и Yandex DataLens
ProductStar29 990 ₽2999 ₽/мес1 месяц
Перейти на сайт курса Обзор

Больше программ — в полном каталоге курсов по SQL

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

Что такое pivot в SQL простыми словами?

Это поворот таблицы: значения одного столбца, например месяцы, становятся заголовками новых столбцов, а в ячейки попадает сумма, количество или другой агрегат по строкам с этим значением.

Есть ли PIVOT в PostgreSQL и MySQL?

Отдельного оператора нет. В PostgreSQL сводную таблицу собирают агрегатом с FILTER, через CASE или функцией crosstab из расширения tablefunc, в MySQL через SUM с CASE и GROUP BY.

Чем pivot отличается от транспонирования?

Транспонирование меняет строки и столбцы местами без вычислений. Pivot группирует строки и сводит значения агрегатом: две сделки за январь превращаются в одну ячейку с их суммой.

Как сделать pivot, если столбцы заранее неизвестны?

Собрать текст запроса динамически: в MySQL через GROUP_CONCAT и PREPARE, в SQL Server через динамический SQL. В Oracle есть PIVOT XML, но он возвращает один столбец с XML, а в консоли psql столбцы можно не перечислять, если выполнить запрос командой \crosstabview.

Что делает UNPIVOT?

Обратную операцию: превращает столбцы в строки. Исходную таблицу он не восстанавливает, потому что сложенные при повороте значения уже не разделить, а строки с NULL из результата пропадают.