Преобразование строк в столбцы с Pivot в SQL Server
Быстрый ответ
PIVOT
в SQL Server – это удобный инструмент для трансформации строк в столбцы. Давайте рассмотрим применение данного подхода на примере:
-- Выбираем данные по Продуктам и Годам
SELECT Product, [2020], [2021], [2022]
FROM Sales
PIVOT (
-- Суммируем общие продажи
SUM(TotalSales) FOR Year IN ([2020], [2021], [2022])
) AS PivotTable; -- Получаем результирующую таблицу
В данном примере уникальные значения Year
преобразуются в названия столбцов, формируя таким образом сводные данные TotalSales
по продуктам (Product
) за каждый год. Вы можете использовать любые другие столбцы, соответствующие вашей задаче.
Сценарий "удиви меня"
Иногда имена столбцов бывают столь же непредсказуемы, как поведение кота на горячей крыше. В случае работы с динамическими названиями столбцов, такими как, например, номера недель, можно применить динамический SQL:
-- Выбираем потенциальные названия столбцов
SELECT DISTINCT CONVERT(VARCHAR, WeekNumber) AS WeekNumber
FROM Sales;
Затем необходимо создать строку SQL и выполнить ее при помощи функции sp_executesql
. Рассмотрим детальнее этот процесс:
-- Подготавливаем переменные для хранения наших значений
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
их можно заменить на нули или любые другие значение, в зависимости от ваших требований:
-- Заменяем 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
может наблюдаться замедление работы. Для предотвращения подобного развития событий можно использовать временные таблицы или подзапросы, что увеличит производительность и улучшит читаемость кода:
-- Облегчаем нагрузку на систему, заранее организовывая данные
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
также отлично работает со специфическими типами данных и может представить данные в альтернативном виде:
-- Посмотрим на данные под новым углом
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;
Визуализация
Возьмем для примера строки данных, представляющие собой вагоны поезда:
Строки (🚂): [Станция 1, Станция 2, Станция 3]
Используя PIVOT
, мы "переворачиваем" этот поезд таким образом, что станции становятся столбцами:
Столбцы (🎡):
┌── Станция 1
🚂 -> │── Станция 2
└── Станция 3
Вот как PIVOT
в SQL преобразует данные:
-- Процесс переформатирования завершен
SELECT * FROM
( SELECT Station, Passenger FROM Boarding ) AS BoardingTable
PIVOT
( COUNT(Passenger) FOR Station IN ([Станция 1], [Станция 2], [Станция 3]) ) AS PivotTable;
Теперь каждая станция сопоставлена с количеством пассажиров.
Продвинутое преобразование
Сложные схемы? Нет проблем
Чтобы работать с различными именами таблиц и их схемами, можно использовать хранимые процедуры:
-- Хранимые процедуры как эффективное решение
CREATE PROCEDURE DynamicPivotTable @TableName NVARCHAR(128)
AS
BEGIN
DECLARE @DynamicPivotQuery NVARCHAR(MAX);
-- Создаем запрос на основе @TableName
END
Группировка для большей ясности
Хотите предварительно сгруппировать строки перед преобразованием? Добавьте GROUP BY
:
-- Группируем данные
SELECT Customer, [Понедельник], [Вторник], [Среда]
FROM Sales
PIVOT (
SUM(TotalSales) FOR Weekday IN ([Понедельник], [Вторник], [Среда])
) AS PivotTable
GROUP BY Customer;
Динамическое преобразование с помощью курсора
Курсоры могут быть полезны, когда названия столбцов изменяются со скоростью модных трендов:
-- Курсоры как надёжные помощники для работы со столбцами данных
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.