PIVOT и UNPIVOT в Transact-SQL – описание и примеры использования операторов

Сегодня мы поговорим о таких операторах Transact-SQL как PIVOT и UNPIVOT, узнаем, для чего они нужны, рассмотрим синтаксис написания запросов, и, конечно же, разберем примеры использования их на практике.

В Transact-SQL для написания перекрестных запросов или кросс табличных выражений существует специальный оператор, я бы сказал даже целая конструкция под названием PIVOT, которая имеет достаточно специфический синтаксис, также существует оператор, который делает и обратное действие он называется как не странно UNPIVOT. Эти операторы мы сейчас подробно рассмотрим, и для начала давайте я расскажу, как будет выглядеть данная статья.

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

Примечание! Все примеры мы будем рассматривать в СУБД MS SQL Server 2014 Express с использованием Management Studio.

Оператор PIVOT

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

Данный оператор может потребоваться тогда, когда необходимо, например, предоставить какой либо отчет в наглядной форме по годам, допустим для бухгалтеров и экономистов, так как именно они любят представления данных в таком виде. Также он может пригодиться и просто для преставления какой-либо статистики, но в любом случае из собственного опыта могу сказать, что оператор PIVOT будет требоваться достаточно редко, но когда он потребуется он будет просто незаменим и очень полезен, поэтому Вы должны знать, как и когда его можно использовать.

Результат, который мы получим при использовании оператора PIVOT, можно также получить и с использованием известной конструкции select…case, а до появления MS SQL сервера 2005 только с использованием этой конструкции, как Вы правильно поняли, оператор PIVOT можно использовать, только начиная с 2005 sql сервера.

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

Синтаксис оператора PIVOT

SELECT столбец для группировки,  [значения по горизонтали],…

FROM таблица или подзапрос

PIVOT(агрегатная функция

FOR столбец, содержащий значения, которые станут именами столбцов

IN ([значения по горизонтали],…)

)AS псевдоним таблицы (обязательно)

в случае необходимости ORDER BY;

Вот такой вот синтаксис, помимо всего прочего значения, которые будут выступать в качестве названия колонок по горизонтали, необходимо писать вручную, т.е. мы должны знать их заранее, другими словами, динамический запрос построить, не получиться. На самом деле можно, с помощью динамического формирования строки запроса, а потом исполнение этой строки через специальную команду EXECUTE, но как говорится это уже совсем другая история (для начинающих рекомендую прочитать наш справочник по Transact-SQL).

Пример использования оператора PIVOT

С теорией я думаю достаточно, поэтому давайте переходить к практике, тем более что на примерах лучше понять, как же работает этот оператор.

И для начала давайте разберем исходные данные.

Допустим, у нас есть таблица вот с такой структурой:

 
   CREATE TABLE [dbo].[test_table_pivot](
        [fio] [varchar](50) NULL,
        [god] [int] NULL,
        [summa] [float] NULL
   ) ON [PRIMARY]
   GO

Где, fio — это ФИО сотрудника, god – год, в котором он получал премию, summa — соответственно сумма премии, вот такой незамысловатый пример, так как в плоскости времени наглядней видна работа оператора PIVOT.

И в данной таблице у нас есть тестовые данные, для просмотра этих данных напишем простой запрос на выборку, т.е. select

  
   SELECT * FROM dbo.test_table_pivot

Скриншот 1

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

Самым простым способом будет конечно просто использовать конструкцию GROUP BY, например

  
   SELECT fio, god, sum(summa) AS summa 
   FROM dbo.test_table_pivot
   GROUP BY fio, god

Скриншот 2

На что нам начальник скажет, что это такое? ничего не понятно? не наглядно? Улучшить ситуацию можно, добавив еще и сортировку ORDER BY, допустим сначала по фамилии, а затем по году

  
   SELECT fio, god, sum(summa) as summa 
   FROM dbo.test_table_pivot
   GROUP BY fio, god
   ORDER BY fio, god

но это все равно не то. А вот если мы будем использовать оператор PIVOT, например вот таким образом

   
   SELECT fio, [2011], [2012], [2013], [2014], [2015]
   FROM dbo.test_table_pivot
   PIVOT (SUM(summa)for god in ([2011],[2012],[2013],[2014],[2015])
           ) AS test_pivot

то у нас получится вот такой результат

Скриншот 3

Я думаю, Вы согласитесь, что так намного наглядней и понятней.

Здесь у нас:

  • fio —  столбец, по которому мы будем осуществлять группировку;
  • [2011],[2012],[2013],[2014],[2015] — названия наших столбцов по горизонтали, ими выступают значения из колонки god;
  • sum(summa) — агрегатная функция по столбцу summa;
  • for god in ([2011],[2012],[2013],[2014],[2015]) — тут мы указываем колонку, в которой содержатся значения, которые будут выступать в качестве названия наших результирующих столбцов, по факту в скобках мы указываем то же самое, что и чуть выше в select;
  • as test_pivot —  это обязательный псевдоним, не забывайте его указывать, иначе будет ошибка.

Переходим к UNPIVOT.

Оператор UNPIVOT

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

UNPIVOT требуется еще реже, чем PIVOT, но о нем также необходимо знать.

Здесь я думаю, давайте сразу перейдем к рассмотрению примера.

Пример использования UNPIVOT

Допустим, таблица имеет следующую структуру:

   
   CREATE TABLE [dbo].[test_table_unpivot](
        [fio] [varchar](50) NULL,
        [number1] [int] NULL,
        [number2] [int] NULL,
        [number3] [int] NULL,
        [number4] [int] NULL,
        [number5] [int] NULL,
   ) ON [PRIMARY]
   GO

Где, fio — ФИО сотрудника, а number1, number2… и так далее это какие-то номера этого сотрудника:)

Данные будут, например, такие:

Скриншот 4

И допустим, нам необходимо развернуть эту таблицу, для этого мы будем использовать оператор UNPIVOT, а запрос будет выглядеть следующим образом:

  
   SELECT fio, column_name, number
   FROM dbo.test_table_unpivot
   UNPIVOT(
          number for column_name in (
                                     [number1],[number2],[number3],[number4],[number5]
                                     )
          )AS test_unpivot

Скриншот 5

Где,

  • fio – столбец с ФИО, он в принципе не изменился;
  • column_name – псевдоним столбца, который будет содержать названия наших колонок;
  • number – псевдоним для значений из столбцов number1, number2…

Заметка! Начинающим программистам рекомендую почитать мою книгу «Путь программиста T-SQL. Самоучитель по языку Transact-SQL», в ней я подробно, с большим количеством примеров, рассказываю про другие возможности языка Transact-SQL.

На этом все, удачи!

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Комментариев: 4
  1. Аватар
    qwez

    А как быть если:
    в исходной таблице,допусти м, добавлен столбик «месяц события», тогда в итоговом результате необходимо отобразить таблицу с,так сказать, «духстрочными» заголовками где в первой строке год(как в примере) а второй заглавный столбик- месяц. В новой табл результатов на пересечении строки «фио» и столбца с названием месяца будет значение поля «summa» исходной таблицы.
    Реально ли такое реализовать методами sql?

  2. Аватар
    Дмитрий

    А как сделать так, чтобы года не перечислять в запросе? Например, если этих годов сотни, их что, так вручную и записывать?

    1. Админ
      Админ (автор)

      В рамках данного простого запроса да, но можно написать динамическую процедуру (инструкцию), в которой Вы можете года формировать динамически, например, на основе запроса.
      Суть заключается в формировании строки запроса и выполнении ее с помощью EXECUTE

  3. Админ
    Админ (автор)

    Вот пример реализации динамического PIVOT

Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.