реклама
разместить

Как с помощью 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
реклама
разместить
Начать дискуссию
Apple представила iPad Air с чипом M3 и базовый iPad с чипом A16

А также обновлённую клавиатуру Magic Keyboard с трекпадом для iPad Air.

Источник здесь и далее — Apple 
88
11
реклама
разместить
Nothing представила Phone (3a) и (3a) Pro с тремя основными камерами и встроенными функциями ИИ

Цены на устройства — меньше $500.

Источник здесь и далее: Nothing
99
55
44
11
Это настоящий УБИЙЦА АЙФОНА
Слив денег в рекламных компаниях.
Слив денег в рекламных компаниях.

Соответственно, на каких позициях бы карточка не стояла, такой трафик в поиске по данным ключам получить невозможно, а СТР прямо указывает нам на то, что показы были супер нецелевыми.

США начали обсуждать план по смягчению санкций против России — Reuters

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

3535
1515
22
22
22
11
11
11
На переговоры с трампом нужно посылать не чиновников, а специалистов, которые разводят пенсионеров и ветироанов на миллионы рублей. Они американского деда так опрокинут, что он даже ничего не поймет, выполнит все условия и еще аляску взад подарит.
Правительство не продлило мораторий на выездные налоговые проверки ИТ-компаний

Его действие завершилось 3 марта 2025 года.

Фото РБК
1010
99
11
Visa и Mastercard: камбэк в Россию. Надежды, реальность и альтернативы.
Visa и Mastercard: камбэк в Россию. Надежды, реальность и альтернативы.

Недавно российское сообщество всколыхнула новость о том, что якобы Visa и Mastercard вернутся в Россию. Вы же помните те времена, карты этих платежных систем, выпущенные российскими банками, без проблем работали за рубежом. Увы, сейчас они котируются только в пределах родной страны. Так есть ли шанс, что международные платежные системы вернутся в Р…

1313
77
55
Как понять, почему твой бизнес не растёт?

Каждый предприниматель хочет видеть рост своего бизнеса. Но что делать, если компания работает, маркетинг запущен, продажи идут, а роста нет? Сейчас помогу вам разобраться, в чём причина застоя и какие действия помогут сдвинуть бизнес с мёртвой точки.

Если бизнес не растёт, значит, где-то есть слабые звенья, мешающие развитию. Важно не надеяться на чудо, а системно разбираться с проблемами. Найдите точки роста, устраните узкие места – и ваш бизнес снова начнёт двигаться вперёд!

Тайваньская TSMC инвестирует $100 млрд для строительства заводов по производству чипов в США

Общие инвестиции компании в Соединённые Штаты составят $165 млрд.

55
44
22
11
Новости инвестиций в России: - Сбер забрал Нетологию за долги - Фонд инвестировал $3m на излечение от деменции - Инвестор потратит $1м чтобы построить космическую станцию на Луне
[]