Сегодня мы поговорим о таких операторах 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, но как говорится это уже совсем другая история.
Заметка! Начинающим рекомендую посмотреть мой видеокурс по T-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…
Заметка! Начинающим программистам рекомендую почитать мою книгу «SQL код», которая поможет Вам изучить язык SQL как стандарт, в ней рассматриваются все базовые конструкции языка SQL, приводится много примеров и скриншотов.
Для профессионального изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL.
Видеоурок
На этом все, удачи!
А как быть если:
в исходной таблице,допусти м, добавлен столбик «месяц события», тогда в итоговом результате необходимо отобразить таблицу с,так сказать, «духстрочными» заголовками где в первой строке год(как в примере) а второй заглавный столбик- месяц. В новой табл результатов на пересечении строки «фио» и столбца с названием месяца будет значение поля «summa» исходной таблицы.
Реально ли такое реализовать методами sql?
А как сделать так, чтобы года не перечислять в запросе? Например, если этих годов сотни, их что, так вручную и записывать?
В рамках данного простого запроса да, но можно написать динамическую процедуру (инструкцию), в которой Вы можете года формировать динамически, например, на основе запроса.
Суть заключается в формировании строки запроса и выполнении ее с помощью EXECUTE
Вот пример реализации динамического PIVOT