Преобразование строк в столбцы с 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) за каждый год. Вы можете использовать любые другие столбцы, соответствующие вашей задаче.

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

Иногда имена столбцов бывают столь же непредсказуемы, как поведение кота на горячей крыше. В случае работы с динамическими названиями столбцов, такими как, например, номера недель, можно применить динамический 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.

Проверь как ты усвоил материалы статьи

Пройди тест и узнай насколько ты лучше других читателей

Что делает оператор PIVOT в SQL Server?

1 / 5

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

Системные требования для установки Swift

6 сентября 2024

Вставка и редактирование изображений в Google Презентациях

6 сентября 2024

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

6 сентября 2024