Оператор PIVOT и его аналоги: сворачивание данных в таблицу

Зачем нужен PIVOT? Превращаем строки в столбцы!

Представьте, что у вас есть таблица с продажами в таком виде:

Месяц	Товар	Количество
Январь	Яблоки	100
Январь	Груши	50
Февраль	Яблоки	80
Февраль	Груши	70

А вам нужен вот такой отчёт:

Месяц	Яблоки	Груши
Январь	100	50
Февраль	80	70

Вот для этого и существует оператор PIVOT! Он "переворачивает" данные, преобразуя уникальные значения из одного столбца в новые столбцы.

Как работает PIVOT в SQL Server

Синтаксис оператора PIVOT в MS SQL Server:

SELECT [столбцы_не_для_сворачивания],
       [значение1] AS [новый_столбец1],
       [значение2] AS [новый_столбец2]
FROM исходная_таблица
PIVOT (
    агрегатная_функция(столбец_значений)
    FOR столбец_для_сворачивания IN ([значение1], [значение2], ...)
) AS псевдоним

Пример из жизни:

-- Преобразуем продажи по месяцам
SELECT Месяц, [Яблоки], [Груши]
FROM (
    SELECT Месяц, Товар, Количество 
    FROM Продажи
) AS ИсходныеДанные
PIVOT (
    SUM(Количество)
    FOR Товар IN ([Яблоки], [Груши])
) AS PivotTable

🔍 Разберём по шагам: 1. В подзапросе выбираем только нужные столбцы 2. SUM(Количество) — агрегируем значения 3. FOR Товар IN ([Яблоки], [Груши]) — превращаем уникальные значения столбца "Товар" в новые столбцы

Альтернативы PIVOT в других СУБД

Не все базы данных поддерживают PIVOT, но есть универсальные способы:

1. Условная агрегация с CASE

SELECT 
    Месяц,
    SUM(CASE WHEN Товар = 'Яблоки' THEN Количество ELSE 0 END) AS Яблоки,
    SUM(CASE WHEN Товар = 'Груши' THEN Количество ELSE 0 END) AS Груши
FROM Продажи
GROUP BY Месяц

2. Фильтрованные агрегатные функции (PostgreSQL)

SELECT
    Месяц,
    SUM(Количество) FILTER (WHERE Товар = 'Яблоки') AS Яблоки,
    SUM(Количество) FILTER (WHERE Товар = 'Груши') AS Груши
FROM Продажи
GROUP BY Месяц

3. Функция crosstab() в PostgreSQL

SELECT * FROM crosstab(
  'SELECT Месяц, Товар, SUM(Количество)
   FROM Продажи
   GROUP BY Месяц, Товар
   ORDER BY 1,2',
  'SELECT DISTINCT Товар FROM Продажи ORDER BY 1'
) AS ct (Месяц text, Яблоки int, Груши int);

Продвинутые техники PIVOT

Динамический PIVOT

Когда заранее неизвестно, какие значения нужно преобразовать в столбцы:

DECLARE @columns NVARCHAR(MAX) = '';
DECLARE @sql NVARCHAR(MAX) = '';

-- Формируем список столбцов динамически
SELECT @columns = @columns + '[' + Товар + '],'
FROM (SELECT DISTINCT Товар FROM Продажи) AS tmp;

SET @columns = LEFT(@columns, LEN(@columns) - 1);

-- Строим динамический запрос
SET @sql = '
SELECT Месяц, ' + @columns + '
FROM (
    SELECT Месяц, Товар, Количество
    FROM Продажи
) AS ИсходныеДанные
PIVOT (
    SUM(Количество)
    FOR Товар IN (' + @columns + ')
) AS PivotTable';

EXEC sp_executesql @sql;

PIVOT с несколькими агрегатными функциями

SELECT Месяц, [Яблоки_Кол], [Яблоки_Сум], [Груши_Кол], [Груши_Сум]
FROM (
    SELECT Месяц, Товар, Количество, Количество * Цена AS Сумма
    FROM Продажи
) AS ИсходныеДанные
PIVOT (
    SUM(Количество) FOR Товар IN ([Яблоки_Кол]) 
) AS PivotQuantity
PIVOT (
    SUM(Сумма) FOR Товар IN ([Яблоки_Сум], [Груши_Сум])
) AS PivotAmount;

Когда использовать PIVOT?

✅ Идеальные сценарии:

Создание отчётов с перекрёстной табуляцией
Преобразование данных для визуализации
Анализ данных с группировкой по категориям

❌ Когда лучше избегать:

При очень большом количестве уникальных значений (может создать сотни столбцов)
Когда данные часто меняются (динамический PIVOT сложен в поддержке)
В высоконагруженных OLTP-системах

Главные ошибки новичков

Забывают агрегатную функцию
PIVOT всегда требует агрегации (SUM, AVG, COUNT и т.д.)
Не указывают все возможные значения
Если в исходных данных появится новое значение, его нужно добавить в IN
Путают порядок столбцов
Столбцы в PIVOT выводятся в порядке, указанном в IN
Игнорируют NULL-значения
Можно использовать COALESCE или ISNULL для замены NULL на 0

Практическое задание

День	Студент	Часы
Понедельник	Иванов	4
Понедельник	Петров	3
Вторник	Иванов	2
Вторник	Сидоров	5

В таблицу:

День	Иванов	Петров	Сидоров
Понедельник	4	3	NULL
Вторник	2	NULL	5