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