Преобразование строк в столбцы с Pivot в SQL Server

Пройдите тест, узнайте какой профессии подходите

Я предпочитаю

Работать самостоятельно и не зависеть от других

Работать в команде и рассчитывать на помощь коллег

Организовывать и контролировать процесс работы

Быстрый ответ

PIVOT в SQL Server – это удобный инструмент для трансформации строк в столбцы. Давайте рассмотрим применение данного подхода на примере:

SQL

Скопировать код

-- Выбираем данные по Продуктам и Годам
SELECT Product, [2020], [2021], [2022]
FROM Sales
PIVOT (
    -- Суммируем общие продажи
    SUM(TotalSales) FOR Year IN ([2020], [2021], [2022])
) AS PivotTable;  -- Получаем результирующую таблицу

В данном примере уникальные значения Year преобразуются в названия столбцов, формируя таким образом сводные данные TotalSales по продуктам (Product) за каждый год. Вы можете использовать любые другие столбцы, соответствующие вашей задаче.

Кинга Идем в IT: пошаговый план для смены профессии

Сценарий "удиви меня"

Иногда имена столбцов бывают столь же непредсказуемы, как поведение кота на горячей крыше. В случае работы с динамическими названиями столбцов, такими как, например, номера недель, можно применить динамический SQL:

SQL

Скопировать код

-- Выбираем потенциальные названия столбцов
SELECT DISTINCT CONVERT(VARCHAR, WeekNumber) AS WeekNumber
FROM Sales;

Затем необходимо создать строку SQL и выполнить ее при помощи функции sp_executesql. Рассмотрим детальнее этот процесс:

SQL

Скопировать код

-- Подготавливаем переменные для хранения наших значений
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

-- Объединяем уникальные столбцы
SELECT @columns = STRING_AGG(QUOTENAME(WeekNumber), ', ') FROM (SELECT DISTINCT WeekNumber FROM Sales) AS Weeks;

-- Формируем наш SQL запрос
SET @sql = '
SELECT *
FROM Sales
PIVOT (
    SUM(TotalSales)
    FOR WeekNumber IN (' + @columns + ') -- PIVOT делает необходимые преобразования
) AS PivotTable;';

-- Выполняем сформированный запрос
EXEC sp_executesql @sql;

Обработка пустых значений

При применении PIVOT в обработанном результате могут появиться пустые поля (null). С помощью функции ISNULL их можно заменить на нули или любые другие значение, в зависимости от ваших требований:

SQL

Скопировать код

-- Заменяем null на ноль.
SELECT Product, ISNULL([2020], 0) AS [2020], ISNULL([2021], 0) AS [2021], ISNULL([2022], 0) AS [2022]
FROM Sales
PIVOT (
    SUM(TotalSales) FOR Year IN ([2020], [2021], [2022])
) AS PivotTable;

Оптимизация производительности и читаемости

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

SQL

Скопировать код

-- Облегчаем нагрузку на систему, заранее организовывая данные
WITH Sales_CTE AS (
    SELECT Product, Year, TotalSales FROM Sales
)
SELECT Product, [2020], [2021], [2022]
FROM Sales_CTE
PIVOT (
    SUM(TotalSales) FOR Year IN ([2020], [2021], [2022])
) AS PivotTable;

Функция-хамелеон

Для преобразования данных подойдет не только функция SUM. MAX также отлично работает со специфическими типами данных и может представить данные в альтернативном виде:

SQL

Скопировать код

-- Посмотрим на данные под новым углом
SELECT Product, MAX([2020]) AS [2020], MAX([2021]) AS [2021], MAX([2022]) AS [2022]
FROM Sales
PIVOT (
    MAX(CustomerComment) FOR Year IN ([2020], [2021], [2022])
) AS PivotTable;

Визуализация

Возьмем для примера строки данных, представляющие собой вагоны поезда:

Markdown

Скопировать код

Строки (🚂): [Станция 1, Станция 2, Станция 3]

Используя PIVOT, мы "переворачиваем" этот поезд таким образом, что станции становятся столбцами:

Markdown

Скопировать код

Столбцы (🎡):
      ┌── Станция 1
🚂 -> │── Станция 2
      └── Станция 3

Вот как PIVOT в SQL преобразует данные:

SQL

Скопировать код

-- Процесс переформатирования завершен
SELECT * FROM
( SELECT Station, Passenger FROM Boarding ) AS BoardingTable
PIVOT
( COUNT(Passenger) FOR Station IN ([Станция 1], [Станция 2], [Станция 3]) ) AS PivotTable;

Теперь каждая станция сопоставлена с количеством пассажиров.

Продвинутое преобразование

Сложные схемы? Нет проблем

Чтобы работать с различными именами таблиц и их схемами, можно использовать хранимые процедуры:

SQL

Скопировать код

-- Хранимые процедуры как эффективное решение
CREATE PROCEDURE DynamicPivotTable @TableName NVARCHAR(128)
AS
BEGIN
    DECLARE @DynamicPivotQuery NVARCHAR(MAX);
    -- Создаем запрос на основе @TableName
END

Группировка для большей ясности

Хотите предварительно сгруппировать строки перед преобразованием? Добавьте GROUP BY:

SQL

Скопировать код

-- Группируем данные
SELECT Customer, [Понедельник], [Вторник], [Среда]
FROM Sales
PIVOT (
    SUM(TotalSales) FOR Weekday IN ([Понедельник], [Вторник], [Среда])
) AS PivotTable
GROUP BY Customer;

Динамическое преобразование с помощью курсора

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

SQL

Скопировать код

-- Курсоры как надёжные помощники для работы со столбцами данных
DECLARE @ColumnName NVARCHAR(128), @PivotQuery NVARCHAR(MAX);

DECLARE columnCursor CURSOR FOR 
SELECT DISTINCT ColumnName FROM TableName;

OPEN columnCursor;
FETCH NEXT FROM columnCursor INTO @ColumnName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Преобразуем каждый столбец по очереди
    FETCH NEXT FROM columnCursor INTO @ColumnName;
END

CLOSE columnCursor;
DEALLOCATE columnCursor;

Полезные материалы

Использование PIVOT и UNPIVOT – Документация Microsoft.
Несколько способов преобразования строк в столбцы – Различные подходы к упорядочиванию данных в таблицах.
SQL Server: Оператор PIVOT – Универсальный инструмент для работы с данными.
Избегайте использования Not Equal в WHERE Clause – Все грани равенства и неравенства в SQL.

Свежие материалы

Протоколы уровня представления: примеры и использование

6 сентября 2024

Оптимизация скорости загрузки сайта

6 сентября 2024

Вопросы по SQL на собеседовании для тестировщиков

6 сентября 2024