Динамический PIVOT в T-SQL — универсальная процедура формирования запроса

Привет! Если Вы задаетесь вопросом, как сформировать динамический перекрёстный SQL запрос, а именно динамический запрос PIVOT, то данный материал будет Вам полезен, так как здесь я покажу, как написать универсальную процедуру, с помощью которой можно формировать динамические запросы PIVOT.

Введение

Если Вы читаете данный материал, значит Вы уже столкнулись с проблемой, с которой сталкиваются все, когда начинают использовать конструкцию PIVOT в Microsoft SQL Server. О том, что это за конструкция можете почитать в материале – PIVOT и UNPIVOT в Transact-SQL.

Сам оператор PIVOT, конечно же, очень полезен, так как с помощью него мы можем формировать результирующий набор данных в очень удобном виде. Если вкратце, то с помощью PIVOT мы можем формировать сводные таблицы, а именно осуществлять транспонирование таблицы (в Excel это так называется) с агрегацией и группировкой данных. Иными словами, значения в столбце, которые расположены в таблице соответственно по вертикали, мы можем выстроить по горизонтали. PIVOT – это практически незаменимый инструмент в аналитике данных на языке Transact-SQL.

Но, как я уже сказал, у него есть один недостаток, проблема заключается в следующем, синтаксис PIVOT требует перечисления вручную имен столбцов, которые мы хотим «развернуть».

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

Только эти года, как я уже сказал, в запросе мы должны написать, т.е. перечислить сами. А если этих годов много? Или если это не года, а какая-нибудь другая характеристика, которая динамически изменяется? Мы просто не сможем это сделать.

Однако с помощью языка T-SQL мы можем динамически сформировать перечень этих годов, что позволит нам анализировать данные, изначально даже не зная, сколько там годов и какие именно.

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

Примечание! Оператор PIVOT появился только в 2005 версии SQL Server, в более ранних версиях он недоступен. Все примеры в данном материале выполнены в Microsoft SQL Server 2016 Express и тестировались на более ранних версиях.

Так как материал подразумевает наличие у Вас определенных знаний языка T-SQL, я рекомендую начинающим, если Вам что-то непонятно, ознакомиться со следующей информацией:

• Видеокурсы по T-SQL
• Чем отличаются функции от хранимых процедур в T-SQL
• Что такое T-SQL. Подробное описание для начинающих
• SQL код – самоучитель по SQL для начинающих программистов

Процедура для формирования динамического SQL запроса PIVOT

Данная процедура универсальная, необходимо при запуске только передать соответствующие параметры, остальное процедура сделает сама.

Параметры следующие:

• @TableSRC — имя таблицы или представления;
• @ColumnName — столбец, содержащий значения, которые станут именами столбцов;
• @Field — столбец, над которым проводить агрегацию данных, т.е. к которому применять статистическую функцию;
• @FieldRows — столбец, по которому необходимо сгруппировать данные по строкам;
• @FunctionType — агрегатная функция (SUM, COUNT, MAX, MIN, AVG), по умолчанию SUM, т.е. параметр указывать необязательно;
• @Condition – условие, тоже необязательный параметр.

Ниже представлен код хранимой процедуры, я его подробно прокомментировал.

   
   -- Создаем универсальную процедуру для динамического PIVOT   
   CREATE PROCEDURE SP_Dynamic_Pivot
   (
        @TableSRC NVARCHAR(100),   --Таблица источник (Представление)
        @ColumnName NVARCHAR(100), --Столбец, содержащий значения, которые станут именами столбцов
        @Field NVARCHAR(100),      --Столбец, над которым проводить агрегацию
        @FieldRows NVARCHAR(100),  --Столбец (столбцы) для группировки по строкам (Column1, Column2)
        @FunctionType NVARCHAR(20) = 'SUM',--Агрегатная функция (SUM, COUNT, MAX, MIN, AVG), по умолчанию SUM
        @Condition NVARCHAR(200) = '' --Условие (WHERE и т.д.). По умолчанию без условия
   )
   AS 
   BEGIN
        /*
                Универсальная процедура формирования динамического запроса PIVOT.
                Разработчик Info-Comp.ru
        */
        
        --Отключаем вывод количества строк
        SET NOCOUNT ON;
        
        --Переменная для хранения строки запроса
        DECLARE @Query NVARCHAR(MAX);                     
         --Переменная для хранения имен столбцов
        DECLARE @ColumnNames NVARCHAR(MAX);              
        --Переменная для хранения заголовков результирующего набора данных
        DECLARE @ColumnNamesHeader NVARCHAR(MAX); 

        --Обработчик ошибок
        BEGIN TRY
                --Таблица для хранения уникальных значений, 
                --которые будут использоваться в качестве столбцов      
                CREATE TABLE #ColumnNames(ColumnName NVARCHAR(100) NOT NULL PRIMARY KEY);
        
                --Формируем строку запроса для получения уникальных значений для имен столбцов
                SET @Query = N'INSERT INTO #ColumnNames (ColumnName)
                                                  SELECT DISTINCT COALESCE(' + @ColumnName + ', ''Пусто'') 
                                                  FROM ' + @TableSRC + ' ' + @Condition + ';'
                
                --Выполняем строку запроса
                EXEC (@Query);

                --Формируем строку с именами столбцов
                SELECT @ColumnNames = ISNULL(@ColumnNames + ', ','') + QUOTENAME(ColumnName) 
                FROM #ColumnNames;
                
                --Формируем строку для заголовка динамического перекрестного запроса (PIVOT)
                SELECT @ColumnNamesHeader = ISNULL(@ColumnNamesHeader + ', ','') 
                                                                        + 'COALESCE('
                                                                        + QUOTENAME(ColumnName) 
                                                                        + ', 0) AS '
                                                                        + QUOTENAME(ColumnName)
                FROM #ColumnNames;
        
                --Формируем строку с запросом PIVOT
                SET @Query = N'SELECT ' + @FieldRows + ' , ' + @ColumnNamesHeader + ' 
                                           FROM (SELECT ' + @FieldRows + ', ' + @ColumnName + ', ' + @Field 
                                                         + ' FROM ' + @TableSRC  + ' ' + @Condition + ') AS SRC
                                           PIVOT ( ' + @FunctionType + '(' + @Field + ')' +' FOR ' +  
                                                                   @ColumnName + ' IN (' + @ColumnNames + ')) AS PVT
                                           ORDER BY ' + @FieldRows + ';'
                
                --Удаляем временную таблицу
                DROP TABLE #ColumnNames;

                --Выполняем строку запроса с PIVOT
                EXEC (@Query);
                
                --Включаем обратно вывод количества строк
                SET NOCOUNT OFF;
                
        END TRY
        BEGIN CATCH
                --В случае ошибки, возвращаем номер и описание этой ошибки
                SELECT ERROR_NUMBER() AS [Номер ошибки], 
                           ERROR_MESSAGE() AS [Описание ошибки]
        END CATCH
   END

Заметка! Описание операторов плана выполнения запроса в Microsoft SQL Server.

Исходные данные для примеров

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

   
   --Инструкция создания таблицы
   CREATE TABLE TestTable(
        [ProductId] INT IDENTITY(1,1) NOT NULL,
        [CategoryName] VARCHAR(100) NOT NULL, 
        [ProductName] VARCHAR(100) NOT NULL,
        [Summa] MONEY NULL,
        [YearSales] INT NOT NULL
   )

   GO
   -- Инструкция добавления данных
   INSERT INTO TestTable(CategoryName, ProductName, Summa, YearSales)
     VALUES ('Комплектующие компьютера', 'Мышь', 100, 2015),
                        ('Комплектующие компьютера', 'Мышь', 110, 2016),
                        ('Комплектующие компьютера', 'Мышь', 120, 2017),
                        ('Комплектующие компьютера', 'Мышь', 130, 2018),
                        ('Комплектующие компьютера', 'Мышь', 130, 2018),
                        ('Комплектующие компьютера', 'Клавиатура', 170, 2016),
                        ('Комплектующие компьютера', 'Клавиатура', 180, 2017),
                        ('Комплектующие компьютера', 'Клавиатура', 190, 2018),
                        ('Комплектующие компьютера', 'Клавиатура', 200, 2018),
                        ('Мобильные устройства', 'Телефон', 400, 2015),
                        ('Мобильные устройства', 'Телефон', 450, 2016),
                        ('Мобильные устройства', 'Телефон', 500, 2017),
                        ('Мобильные устройства', 'Телефон', 550, 2017),
                        ('Мобильные устройства', 'Телефон', 600, 2018)
   GO

   --Запрос на выборку (смотрим, какие данные у нас есть)
   SELECT * FROM TestTable

Примеры использования процедуры формирования динамического PIVOT

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

   
   --Пример 1. Получаем суммы по годам с группировкой по категории
   EXEC SP_Dynamic_Pivot @TableSRC = 'TestTable',  --Таблица источник (Представление)
                                          @ColumnName = 'YearSales',--Столбец, содержащий значения для столбцов в PIVOT
                                          @Field = 'Summa',         --Столбец, над которым проводить агрегацию
                                          @FieldRows = 'CategoryName',--Столбец для группировки по строкам
                                          @FunctionType = 'SUM'     --Агрегатная функция, по умолчанию SUM

Во втором примере давайте осуществим группировку по двум столбцам, при этом мы укажем другую статистическую функцию, не SUM (сумма), а COUNT (количество), т.е. процедура нам вернет количество записей по годам с группировкой по категории и по товару.

   
   --Пример 2. Получаем количество по годам с группировкой по категории и товару
   EXEC SP_Dynamic_Pivot @TableSRC = 'TestTable',  --Таблица источник (Представление)
                                          @ColumnName = 'YearSales',--Столбец, содержащий значения для столбцов в PIVOT
                                          @Field = 'Summa',         --Столбец, над которым проводить агрегацию
                                          @FieldRows = 'CategoryName, ProductName',--Столбцы для группировки по строкам
                                          @FunctionType = 'COUNT'   --Агрегатная функция, по умолчанию SUM

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

Заметка! Представление (VIEW) в T-SQL – описание и примеры использования.

Допустим, в нашем случае нам нужно проанализировать данные только одной категории.

   
   --Пример 3. Получаем суммы по годам с группировкой по товару в конкретной категории
   EXEC SP_Dynamic_Pivot @TableSRC = 'TestTable',  --Таблица источник (Представление)
                                          @ColumnName = 'YearSales',--Столбец, содержащий значения для столбцов в PIVOT
                                          @Field = 'Summa',         --Столбец, над которым проводить агрегацию
                                          @FieldRows = 'ProductName',--Столбец для группировки по строкам
                                          @FunctionType = 'SUM',    --Агрегатная функция, по умолчанию SUM      
                                          @Condition = 'WHERE CategoryName = ''Комплектующие компьютера'''

Надеюсь, процедура будет Вам полезна, удачи!