Как с помощью 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
Начать дискуссию
США начали обсуждать план по смягчению санкций против России — Reuters

Госдеп и Минфин готовят предложение по снятию санкций с отдельных юрлиц и физлиц. С кого именно — неизвестно.

2121
66
22
11
Релоканты и прочие борцуны, что с лицом? Отставить трясску!
реклама
разместить
Стоимость биткоина упала за сутки на 9%, до $83,9 тысячи

Как отмечают аналитики, эффект от новостей о включении некоторых монет в крипторезерв США не мог продлиться долго.

99
66
Безопасный и быстрый обмен на сайте Carlos-Exchange.com

Обменный сервис Carlos-Exchange ваш надежный партнер в сфере обмена криптовалют.


Представляем вашему вниманию наш обменный сервис, который предлагает вам невероятные преимущества.


🔹 Во-первых, это быстрые транзакции. Мы гарантируем, что обмены криптовалюты будут проведены мгновенно, что позволит вам сразу воспользоваться полученными средствами в нуж…

США удвоили пошлины на товары из Китая — с 10% до 20%

Указ о пошлинах приняли в феврале 2025 года.

Источник: Wikimedia
1212
22
11
11
Будут теперь с валдберриз и озона заказывать)
День 1105: ФНС снова может проводить выездные налоговые проверки ИТ-компаний — мораторий на них не продлили

Собираем новости, события и мнения о рынках, банках и реакциях компаний.

Источник: «РИА Новости»
88
Суд приговорил инфоблогера Елену Блиновскую к пяти годам колонии и штрафу в 1 млн рублей

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

Источник: Суды общей юрисдикции города Москвы
5353
3333
99
99
66
11
11
Как двойственно - вроде и поделом за тупое дробление и откровенное инфоцыганство, с другой стороны она от бизнеса а не от чинушья , и как то неприятно, когда сажают человека бизнеса
Производство одежды в Индии. Как найти хорошую фабрику и что обязательно учесть, чтобы не потерять деньги и товар?

Когда бренд одежды хочет масштабировать производство, первая мысль чаще всего — обратиться в Китай. Однако на фоне возросших цен на пошив в Китае все более привлекательным вариантом становится Индия — один из крупнейших мировых экспортеров одежды и текстиля с огромными трудовыми ресурсами.

Фото с одной из наших фабрик-партнеров в Индии
Microsoft представила ИИ-помощника Dragon Copilot для врачей

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

Источник: Microsoft
1919
44
22
[]