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

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

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

  select fio, god, sum(summa) as summa 
  from dbo.test_table_pivot
  group by fio, god

На что нам начальник скажет, что это такое? ничего не понятно? не наглядно? Улучшить ситуацию можно, добавив еще и сортировку 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

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

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

Здесь у нас:

• 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… и так далее это какие-то номера этого сотрудника:)

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

И допустим, нам необходимо развернуть эту таблицу, для этого мы будем использовать оператор 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

Где,

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

На этом все, дальше мы, конечно же, продолжим изучение полезных операторов Transact-SQL, а также других интересных возможностях MS SQL сервера.