Как с помощью Pivot агрегировать информацию из множества столбцов?

Иногда в практике можно столкнуться с задачей, когда необходимо получить выгрузку, которая содержит в себе агрегирующую (сводную) информацию по какому-то объекту (сотруднику, клиенту, счету, карте). Для реализации такого подхода можно использовать оператор pivot в T-SQL, который разворачивает столбец в строку, преобразуя уникальные значения этого столбца в несколько выходных столбцов.

Как с помощью Pivot агрегировать информацию из множества столбцов?

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

Создадим демонстрационную таблицу со списком клиентов и его предложений:

Как с помощью Pivot агрегировать информацию из множества столбцов?

Необходимо получить сводную таблицу по клиенту, для этого дополнительно создадим точки поворота (столбцы number_product_id, number_product_name, number_sales).

WITH CTE_Rank AS ( SELECT Client,product_id,product_name, sales ,number_product_id='i_product_id' + CAST( DENSE_RANK() OVER (PARTITIONBY Client ORDERBY product_id) ASVARCHAR(10) ) ,number_product_name='i_product_name' + CAST( DENSE_RANK() OVER (PARTITIONBY Client ORDERBYproduct_id) ASVARCHAR(10) ) ,number_sales='i_sales' + CAST( DENSE_RANK() OVER (PARTITIONBY Client ORDERBYproduct_id)ASVARCHAR(10)) FROM #T ) select*from CTE_Rank
Как с помощью Pivot агрегировать информацию из множества столбцов?

Добавим в запрос оператор разворота Pivot по каждому из столбцов number_product_id, number_product_name, number_sales.

Рассмотрим синтаксис Pivot на примере одного из столбцов:

  • Client— столбец, по которому мы будем осуществлять группировку
  • MAX(product_id)— агрегатная функция по столбцу product_id, в нашем случае не играет какой-либо роли, но необходимо ее указание согласно синтаксису оператора;
  • FORnumber_product_idin([i_product_id1], [i_product_id2, [i_product_id3])— указание колонки со значениями, которые будут выступать в качестве названия результирующих столбцов
  • AS pivot_id— обязательный псевдоним.
SELECT Client, [i_product_id1], [i_product_id2], [i_product_id3] , [i_product_name1], [i_product_name2], [i_product_name3] , [i_sales1], [i_sales2], [i_sales3] FROM CTE_Rank AS R PIVOT(MAX(product_id) FOR number_product_id IN ([i_product_id1], [i_product_id2], [i_product_id3])) AS pivot_id PIVOT(MAX(product_name) FOR number_product_name IN ([i_product_name1], [i_product_name2], [i_product_name3])) AS pivot_name PIVOT(MAX(sales) FOR number_sales IN ([i_sales1], [i_sales2],[i_sales3])) AS pivot_sales
Как с помощью Pivot агрегировать информацию из множества столбцов?

Результат немного отличается от того, который мы ожидали получить — результаты агрегации выводятся не только в разных колонках, но и в разных строках. Чтобы «схлопнуть» данную выборку, необходимо произвести группировку по столбцам, которые не были результирующими оператора Pivot, в нашем случае это столбец Client.

SELECT Client, i_product_id1 =MAX(i_product_id1), i_product_id2 =MAX(i_product_id2), i_product_id3 =MAX(i_product_id3) , i_product_name1 =MAX([i_product_name1]), i_product_name2 =MAX([i_product_name2]), i_product_name3 =MAX([i_product_name3]) , i_sales1 =MAX([i_sales1]), i_sales2 =MAX([i_sales2]), i_sales3 =MAX([i_sales3]) FROM CTE_Rank AS R PIVOT(MAX(product_id) FOR number_product_id IN ([i_product_id1], [i_product_id2],[i_product_id3])) AS pivot_id PIVOT(MAX(product_name) FOR number_product_name IN ([i_product_name1], [i_product_name2],[i_product_name3])) AS pivot_name PIVOT(MAX(sales) FOR number_sales IN ([i_sales1], [i_sales2],[i_sales3])) AS pivot_sales GROUPBY Client
Как с помощью Pivot агрегировать информацию из множества столбцов?

Теперь результат соответствует поставленной задаче.

Также можно динамически формировать строку запроса, чтоб не перечислять результирующие столбцы Pivot, и выполнять эту сроку через команду Execute.

Использование оператора Pivot существенно облегчает жизнь, но не стоит забывать, что существуют и другие способы, с помощью которых можно получить аналогичный результат, и выбор должен происходить в зависимости от характера поставленной задачи и влияние выбранного способа на общую производительность.

В качестве основы для написания статьи использовалась публикация PIVOT on two or more fields in SQL Server, альтернативный способ использования Pivot для множества столбцов, описан здесь.

22
Начать дискуссию
Суд приговорил инфоблогера Елену Блиновскую к пяти годам колонии и штрафу в 1 млн рублей

Также суд на четыре года запретил ей заниматься коммерческои деятельностью и удовлетворил гражданский иск на 587 млн рублей.

Источник: Суды общей юрисдикции города Москвы
4343
2929
88
77
55
11
11
Как двойственно - вроде и поделом за тупое дробление и откровенное инфоцыганство, с другой стороны она от бизнеса а не от чинушья , и как то неприятно, когда сажают человека бизнеса
реклама
разместить
В сети алкомаркетов «Ароматный мир» начался корпоративный конфликт — СМИ

Суть спора пока неизвестна. В начале марта 2025 года у ритейлера перестали работать сайт и приложение — компания объяснила это сбоем и «переездом» сайта на новый адрес.

99
77
22
11
11
"Ты меня уважаешь?")
Как объединить карточки товаров на Wildberries: пошаговая инструкция
Как объединить карточки товаров на Wildberries: пошаговая инструкция
Microsoft представила ИИ-помощника Dragon Copilot для врачей

Чтобы те «могли сосредоточиться на пациентах, а не компьютере».

Источник: Microsoft
1414
44
11
Создатель бота для «пробива» данных «Глаз Бога» рассказал, что сервис приостановил работу из-за обысков у поставщика данных

Сервис не работает с 28 февраля 2025 года — тогда СМИ сообщили об обысках у команды «Глаза Бога», но основатель бота это опровергал.

1919
66
22
22
11
Законно собирать и сливать персональные данные можно только некоторым компаниям, это их корова и они её доят.
Я производил 2 млн пачек, зарабатывал 55 млн ₽ в год, попал в топ маркетплейсов, но всему приходит конец

В 2023 я больше всех продавал на маркетплейсах в своей товарной категории, работал со всеми крупными ритейл-сетями, а всего бизнес приносил 1,1 млрд ₽ выручки и 55 млн ₽ прибыли в год.

Теперь можно и руки вытереть, и бюджет посчитать 
129129
1919
77
22
11
11
Прямо чистый понедельник, много нового узнал про производство бумажной продукции. Спасибо и удачи автору в начинаниях
Опыт роботизации в МТС Финтех: от хаоса к стандартизации

Роботизация бизнес-процессов (RPA) – не просто модный тренд, а реальный инструмент повышения эффективности компании. В МТС Финтех автоматизация задач с использованием Python позволила значительно оптимизировать рабочие процессы.

Опыт роботизации в МТС Финтех: от хаоса к стандартизации
22
Сервисом по самозапрету на выдачу кредитов воспользовались почти 2 млн человек за два дня

Встроенный помощник «Макс» дал 3,4 млн консультаций о добровольном запрете на кредитование.

1212
33
Теперь каждому, на кого мошенники оформили кредит, начнут говорить: Что ж ты самозапрет не установил? Сам виноват.
[]