MS SQL PIVOT и UNPIVOT: преобразование строк и столбцов

Оператор PIVOT превращает значения строк в столбцы отчета, а UNPIVOT возвращает столбцы в строки. Запрос ms sql pivot подходит для фиксированных категорий, динамический вариант – для меняющегося списка. В статье вы создадите учебную базу и соберете отчеты с фиксированными и динамическими столбцами.

Системные требования и учебная база

PIVOT и UNPIVOT появились в SQL Server 2005. Они работают при уровне совместимости базы 90 и выше.

Примеры используют небольшой синтетический срез данных. Он повторяет структуру MovieLens и не требует скачивания внешнего набора. Таблица [user] хранит пол зрителя, поэтому запросы показывают отчеты по группам M и F.

Создайте таблицы и добавьте пять оценок. Скобки вокруг user защищают имя таблицы от конфликта с ключевыми словами SQL Server.

CREATE TABLE movie (
    movieid INT PRIMARY KEY,
    title NVARCHAR(200) NOT NULL
);

CREATE TABLE [user] (
    userid INT PRIMARY KEY,
    gender CHAR(1) NOT NULL
);

CREATE TABLE rating (
    userid INT NOT NULL,
    movieid INT NOT NULL,
    rating TINYINT NOT NULL
);

INSERT INTO movie (movieid, title) VALUES
(1, N'Фильм 1'), (2, N'Фильм 2');

INSERT INTO [user] (userid, gender) VALUES
(1, 'M'), (2, 'F'), (3, 'M');

INSERT INTO rating (userid, movieid, rating) VALUES
(1, 1, 5), (2, 1, 4), (3, 1, 5),
(1, 2, 3), (2, 2, 4);

Запрос SELECT * FROM rating; покажет пять исходных строк. С ними можно повторить все примеры без импорта большого файла.

Как работает PIVOT

PIVOT берет поле категорий и передает его значения агрегатной функции. Элементы секции IN становятся заголовками результата.

Оставьте в источнике только ключ строки, категорию и значение агрегата. PIVOT группирует строки по всем остальным полям источника. Лишняя колонка создает дополнительные строки с NULL.

WITH orders AS (
    SELECT * FROM (VALUES
        (1, 'new', 10),
        (1, 'done', 7),
        (2, 'new', 5),
        (2, 'done', 8),
        (2, 'done', 2)
    ) AS v(order_id, status, amount)
)
SELECT order_id, [new], [done]
FROM orders
PIVOT (
    SUM(amount)
    FOR status IN ([new], [done])
) AS p
ORDER BY order_id;

Заказ 1 получает 10 в столбце new и 7 в done. Две строки со статусом done для заказа 2 объединяются в сумму 10.

Синтаксис PIVOT и параметры

Источник передает строки оператору, агрегат объединяет значения, а список IN задает столбцы результата.

SELECT <group_column>, [value_1], [value_2]
FROM (<source_query>) AS source_data
PIVOT (
    <aggregate_function>(<value_column>)
    FOR <pivot_column> IN ([value_1], [value_2])
) AS pivot_data;

SUM складывает значения, COUNT считает непустые значения, а AVG вычисляет среднее. Статический список в IN фиксирует структуру результата.

Полный синтаксис операторов описан в документации Microsoft по PIVOT и UNPIVOT.

PIVOT: число оценок фильма по звездам

Используйте статический PIVOT, когда категории известны заранее. В отчете оценки от 1 до 5 превращаются в пять столбцов.

1. Соедините rating и movie по полю movieid. Источник получит название фильма.
2. Соберите поле RatingGrade со значениями от 1_rating до 5_rating.
3. Оставьте в источнике только movieid, title и RatingGrade. PIVOT сохранит одну строку на фильм.
4. Перечислите пять категорий в IN. В результате появятся столбцы с числом оценок.

WITH source_data AS (
    SELECT
        m.movieid,
        m.title,
        CAST(ROUND(r.rating, 0) AS VARCHAR(5)) + '_rating' AS RatingGrade
    FROM rating AS r
    JOIN movie AS m ON m.movieid = r.movieid
)
SELECT movieid, title,
       [1_rating], [2_rating], [3_rating], [4_rating], [5_rating]
FROM source_data
PIVOT (
    COUNT(RatingGrade)
    FOR RatingGrade IN
        ([1_rating], [2_rating], [3_rating], [4_rating], [5_rating])
) AS p
ORDER BY movieid;

GROUP BY ROLLUP в отчете по фильму

ROLLUP добавляет итоги по фильму и общий итог. Строка с пустым Star получает метку t_rating, поэтому PIVOT выводит общее число оценок отдельным столбцом.

Фильтр WHERE [Movie Name] IS NOT NULL убирает общий итог без названия фильма. Без фильтра отчет содержит дополнительную строку с суммой по всем фильмам.

WITH cte_result AS (
    SELECT
        m.movieid,
        m.title,
        ROUND(r.rating, 0) AS rating,
        CAST(ROUND(r.rating, 0) AS VARCHAR(5)) + '_rating' AS Star
    FROM rating AS r
    JOIN movie AS m ON m.movieid = r.movieid
)
SELECT [MovieId], [Movie Name], [1_rating], [2_rating],
       [3_rating], [4_rating], [5_rating], [t_rating]
FROM (
    SELECT
        movieid AS [MovieId],
        title AS [Movie Name],
        COUNT(*) AS noofuser,
        CASE WHEN Star IS NULL THEN 't_rating' ELSE Star END AS RatingGrade
    FROM cte_result
    WHERE movieid <= 2
    GROUP BY ROLLUP(movieid, title, Star)
) AS ratingfilter
PIVOT (
    SUM(noofuser)
    FOR RatingGrade IN
        ([1_rating], [2_rating], [3_rating], [4_rating], [5_rating], [t_rating])
) AS p
WHERE [Movie Name] IS NOT NULL
ORDER BY [MovieId];

Категория без оценок получает NULL. Чтобы показать нули, оберните столбцы внешнего SELECT функцией COALESCE.

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

UNPIVOT превращает несколько столбцов в пары «имя показателя – значение». Такой формат упрощает фильтрацию и подготовку данных для последующего PIVOT.

SELECT <group_column>, <name_column>, <value_column>
FROM <source_table>
UNPIVOT (
    <value_column> FOR <name_column> IN
    (<column_1>, <column_2>)
) AS unpivot_data;

Секция IN перечисляет исходные столбцы. SQL Server записывает их имена в поле метрики, а значения – в поле результата.

WITH film_totals AS (
    SELECT * FROM (VALUES
        (1, N'Фильм 1', CAST(12.0 AS FLOAT),
         CAST(3.0 AS FLOAT), CAST(4.0 AS FLOAT))
    ) AS v(movieid, title, rating_sum, user_count, rating_avg)
)
SELECT movieid, title, metric, value
FROM film_totals
UNPIVOT (
    value FOR metric IN (rating_sum, user_count, rating_avg)
) AS u;

Все разворачиваемые поля приведены к типу FLOAT, потому что UNPIVOT требует совместимые типы. Оператор отбрасывает строки с NULL.

Если нужно сохранить пустые значения, используйте CROSS APPLY (VALUES (...)). Этот способ задает пары вручную и сохраняет строки с NULL.

UNPIVOT + PIVOT: несколько показателей по полу зрителей

Связка операторов меняет форму отчета дважды. Сначала показатели по фильму и полу переходят в строки, затем комбинации пола и метрики становятся столбцами.

1. Соедините таблицы rating, movie и [user] по идентификаторам.
2. Сгруппируйте строки по фильму и полу зрителя.
3. Приведите сумму, количество и среднее к совместимому типу.
4. Объедините пол и имя метрики в поле col.
5. Перечислите комбинации пола и метрики в секции IN.

WITH source_data AS (
    SELECT m.movieid, m.title, r.rating, u.gender
    FROM rating AS r
    JOIN movie AS m ON m.movieid = r.movieid
    JOIN [user] AS u ON u.userid = r.userid
    WHERE r.movieid <= 5
), grouped_data AS (
    SELECT movieid, title,
           CAST(SUM(rating) AS FLOAT) AS rating_sum,
           CAST(COUNT(*) AS FLOAT) AS user_count,
           AVG(CAST(rating AS FLOAT)) AS rating_avg,
           gender
    FROM source_data
    GROUP BY movieid, title, gender
), long_data AS (
    SELECT movieid, title, gender + '_' + metric AS col, value
    FROM grouped_data
    UNPIVOT (
        value FOR metric IN (rating_sum, user_count, rating_avg)
    ) AS u
)
SELECT movieid, title,
       [M_rating_sum], [F_rating_sum],
       [M_user_count], [F_user_count],
       [M_rating_avg], [F_rating_avg]
FROM long_data
PIVOT (
    SUM(value)
    FOR col IN (
        [M_rating_sum], [F_rating_sum],
        [M_user_count], [F_user_count],
        [M_rating_avg], [F_rating_avg]
    )
) AS p
ORDER BY movieid;

Приведение к FLOAT нужно до UNPIVOT, потому что сумма, количество и среднее получают разные числовые типы. Для дробного среднего преобразуйте оценку до вызова AVG.

Условная агрегация через SUM(CASE)

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

SELECT
    movieid,
    SUM(CASE WHEN gender = 'M' THEN rating END) AS male_rating_sum,
    SUM(CASE WHEN gender = 'F' THEN rating END) AS female_rating_sum,
    COUNT(CASE WHEN gender = 'M' THEN 1 END) AS male_user_count,
    COUNT(CASE WHEN gender = 'F' THEN 1 END) AS female_user_count
FROM (
    SELECT r.movieid, r.rating, u.gender
    FROM rating AS r
    JOIN [user] AS u ON u.userid = r.userid
) AS source_data
GROUP BY movieid
ORDER BY movieid;

PIVOT удобнее при одном агрегате и фиксированном списке категорий. SUM(CASE) дает больше контроля при разных условиях и агрегатах, но каждый результат требует отдельного выражения.

Динамический PIVOT для неизвестных значений

Статический PIVOT требует заранее перечислить категории. Динамический вариант собирает список из данных, добавляет его в запрос и запускает готовую строку через sp_executesql.

В примере используется STRING_AGG, доступный в SQL Server 2017 и более новых версиях. В более старых версиях список можно собрать через FOR XML PATH. Сам PIVOT поддерживает более ранние версии SQL Server, поэтому требования к формированию списка отличаются.

DECLARE @columns NVARCHAR(MAX);
DECLARE @query NVARCHAR(MAX);

SELECT @columns = STRING_AGG(QUOTENAME(gender), N',')
FROM (
    SELECT DISTINCT gender
    FROM [user]
    WHERE gender IS NOT NULL
) AS genders;

IF @columns IS NULL
BEGIN
    PRINT N'Нет категорий для отчета';
    RETURN;
END;

SET @query = N'
SELECT movieid, ' + @columns + N'
FROM (
    SELECT r.movieid, u.gender, r.rating
    FROM rating AS r
    JOIN [user] AS u ON u.userid = r.userid
) AS source_data
PIVOT (
    SUM(rating)
    FOR gender IN (' + @columns + N')
) AS p
ORDER BY movieid;';

EXEC sys.sp_executesql @query;

QUOTENAME превращает значения категорий в безопасные идентификаторы. sp_executesql выполняет собранную строку SQL.

Ошибки в запросах PIVOT и UNPIVOT

Перед поворотом выполните исходный SELECT отдельно. Так вы увидите категории, типы данных и лишние поля до усложнения запроса.

• Invalid column name. Проверьте имена полей источника и столбцов внешнего SELECT.
• Лишние строки с NULL. Уберите из источника поле, которое не нужно для ключа, категории или значения.
• Конфликт типов в UNPIVOT. Приведите разворачиваемые поля к одному типу через CAST.
• Пропавшие строки после UNPIVOT. Оператор исключает NULL. Используйте CROSS APPLY (VALUES ...), если NULL должен остаться.
• Пропавшая категория. Статический список в IN не добавляет новые значения. Соберите список динамически.
• Среднее без дробной части. Используйте AVG(CAST(rating AS FLOAT)) для целочисленных оценок.

Как выбрать оператор для отчета

Сопоставьте форму исходных данных с задачей отчета. Фиксированные категории требуют одного решения, переменный список – другого.

Берите PIVOT для фиксированных категорий и одного агрегата. Выбирайте UNPIVOT для формата «метрика – значение». Динамический PIVOT нужен при меняющемся списке, а SUM(CASE) – при нескольких условиях и агрегатах.

Совет от нашей редакции: перед поворотом сохраните исходный SELECT и сравните его строки с результатом отчета. Так лишняя группировка не спрячется за правильными заголовками.