SQL Server PIVOT

В этом руководстве вы узнаете, как использовать оператор PIVOT SQL Server для преобразования строк в столбцы.

Постановка целей

Для демонстрации мы будем использовать таблицы production.products и production.categories из примера базы данных :

Следующий запрос находит количество продуктов для каждой категории продуктов:

SELECT 
    category_name, 
    COUNT(product_id) product_count
FROM 
    production.products p
    INNER JOIN production.categories c 
        ON c.category_id = p.category_id
GROUP BY 
    category_name;

Вот что получилось:

Наша цель — преобразовать названия категорий из первого столбца выходных данных в несколько столбцов и подсчитать количество товаров для каждого названия категории, как показано на следующем рисунке:

Кроме того, мы можем добавить год выпуска модели, чтобы сгруппировать категорию по году выпуска модели, как показано в следующем выводе:

Введение в оператор PIVOT SQL Server

Оператор PIVOT SQL Server вращает табличное выражение. Он преобразует уникальные значения в одном столбце в несколько столбцов в выходных данных и выполняет агрегации по любым оставшимся значениям столбцов.

Чтобы сделать запрос сводной таблицей, выполните следующие действия:

• Сначала выберите базовый набор данных для поворота.
• Во-вторых, создайте временный результат, используя производную таблицу или общее табличное выражение(CTE).
• В-третьих, примените оператор PIVOT.

Давайте применим эти шаги в следующем примере.

Сначала выберите название категории и идентификатор продукта из таблиц production.products и production.categories в качестве базовых данных для сводной таблицы:

SELECT 
    category_name, 
    product_id
FROM 
    production.products p
    INNER JOIN production.categories c 
        ON c.category_id = p.category_id

Во-вторых, создайте временный набор результатов, используя производную таблицу:

SELECT * FROM(
    SELECT 
        category_name, 
        product_id
    FROM 
        production.products p
        INNER JOIN production.categories c 
            ON c.category_id = p.category_id
) t

В-третьих, примените оператор PIVOT:

SELECT * FROM   
(
    SELECT 
        category_name, 
        product_id
    FROM 
        production.products p
        INNER JOIN production.categories c 
            ON c.category_id = p.category_id
) t 
PIVOT(
    COUNT(product_id) 
    FOR category_name IN(
        [Children Bicycles], 
        [Comfort Bicycles], 
        [Cruisers Bicycles], 
        [Cyclocross Bicycles], 
        [Electric Bikes], 
        [Mountain Bikes], 
        [Road Bikes])
) AS pivot_table;

Этот запрос генерирует следующий вывод:

Теперь любой дополнительный столбец, который вы добавляете в список выбора запроса, возвращающего базовые данные, автоматически сформирует группы строк в сводной таблице. Например, вы можете добавить столбец модельного года в приведенный выше запрос:

SELECT * FROM   
(
    SELECT 
        category_name, 
        product_id,
        model_year
    FROM 
        production.products p
        INNER JOIN production.categories c 
            ON c.category_id = p.category_id
) t 
PIVOT(
    COUNT(product_id) 
    FOR category_name IN(
        [Children Bicycles], 
        [Comfort Bicycles], 
        [Cruisers Bicycles], 
        [Cyclocross Bicycles], 
        [Electric Bikes], 
        [Mountain Bikes], 
        [Road Bikes])
) AS pivot_table;

Вот что получилось:

Генерация значений столбцов

В приведенном выше запросе вам пришлось вручную вводить каждое название категории в скобках после оператора IN. Чтобы избежать этого, вы можете использовать функцию QUOTENAME() для генерации списка названий категорий и скопировать их в запрос.

Сначала сгенерируйте список названий категорий:

DECLARE 
    @columns NVARCHAR(MAX) = '';
SELECT 
    @columns += QUOTENAME(category_name) + ','
FROM 
    production.categories
ORDER BY 
    category_name;
SET @columns = LEFT(@columns, LEN(@columns) - 1);
PRINT @columns;

Вывод будет выглядеть так:

[Children Bicycles],[Comfort Bicycles],[Cruisers Bicycles],[Cyclocross Bicycles],[Electric Bikes],[Mountain Bikes],[Road Bikes]

В этом фрагменте:

• Функция QUOTENAME() заключает имя категории в квадратные скобки, например, [Детские велосипеды]
• Функция LEFT() удаляет последнюю запятую из строки @columns.

Во-вторых, скопируйте список названий категорий из выходных данных и вставьте его в запрос.

Динамические сводные таблицы

Если вы добавите новое название категории в таблицу production.categories, вам нужно будет переписать запрос, что не идеально. Чтобы этого не делать, вы можете использовать динамический SQL, чтобы сделать сводную таблицу динамической.

В этом запросе вместо передачи фиксированного списка названий категорий оператору PIVOT мы создаем список названий категорий и передаем его в оператор SQL, а затем динамически выполняем этот оператор с помощью хранимой процедуры sp_executesql.

DECLARE 
    @columns NVARCHAR(MAX) = '', 
    @sql     NVARCHAR(MAX) = '';
-- select the category names
SELECT 
    @columns+=QUOTENAME(category_name) + ','
FROM 
    production.categories
ORDER BY 
    category_name;
-- remove the last comma
SET @columns = LEFT(@columns, LEN(@columns) - 1);
-- construct dynamic SQL
SET @sql ='
SELECT * FROM   
(
    SELECT 
        category_name, 
        model_year,
        product_id 
    FROM 
        production.products p
        INNER JOIN production.categories c 
            ON c.category_id = p.category_id
) t 
PIVOT(
    COUNT(product_id) 
    FOR category_name IN('+ @columns +')
) AS pivot_table;';
-- execute the dynamic SQL
EXECUTE sp_executesql @sql;

В этом руководстве вы узнали, как использовать таблицу SQL Server PIVOT для преобразования строк в столбцы.