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

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

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

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

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

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

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