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 сервера.
- Ограничения в Microsoft SQL Server - что это такое и как их создать?
- Как узнать размер базы данных в Microsoft SQL Server?
- Инструкция INSERT INTO в Transact-SQL – несколько способов добавления данных в таблицу
- Где выгодней обменять электронные деньги (валюту)?
- MX Linux 17.1 – установка и описание дистрибутива
- Вычисляемые столбцы в Transact-SQL
- Инструкция SELECT INTO в T-SQL или как создать таблицу на основе SQL запроса?
- Как установить бесплатную версию антивируса Bitdefender?
- Системная процедура sp_configure - установка параметров конфигурации в Microsoft SQL Server
- Составные операторы присваивания в Transact-SQL
-
«Синий экран смерти» коды ошибок
-
Как прописать статический маршрут? И зачем он нужен?
-
Написание bat файлов - примеры батников
-
Графические оболочки Linux - обзор и сравнение популярных рабочих столов KDE, Xfce, MATE и других
-
Основы программирования и основные языки программирования
-
Основы командной строки Windows
-
Как установить Microsoft .NET Framework 4.0 на Windows 7? и что это такое?
-
10 Причин не включения компьютера
-
Описание и краткая инструкция по работе с Acronis Disk Director Suite
-
Устройство и основные понятия локальной сети
-
Основные настройки Microsoft Windows Server 2003
-
Основы языка WEB программирования HTML
-
Полезная настройка при подключении модема МТС-коннект
-
BAT файлы – Примеры батников (Часть 2)
-
Установка MS SQL Server 2008 Express и Management Studio Express
Комментарии
в исходной таблице,допусти м, добавлен столбик "месяц события", тогда в итоговом результате необходимо отобразить таблицу с,так сказать, "духстрочными" заголовками где в первой строке год(как в примере) а второй заглавный столбик- месяц. В новой табл результатов на пересечении строки "фио" и столбца с названием месяца будет значение поля "summa" исходной таблицы.
Реально ли такое реализовать методами sql?
Суть заключается в формировании строки запроса и выполнении ее с помощью EXECUTE