PIVOT — поворот таблицы в T-SQL
- Автор(ы): Чобиток Василий, 14-15 мая 2010
- В этой статье: о применении инструкций PIVOT и UNPIVOT языка T-SQL для разворота табличных данных.
Содержание
Лирическое вступление
Решив возобновить свои навыки владения SQL, продолжил начатое когда-то решение упражнений на замечательном сайте sql-ex.ru. В отличие от многих других ресурсов, где проводится тестирование путем выбора варианта среди ответов на вопрос, здесь решением является собственный SQL-запрос, который должен выдать верный набор данных на тестовой базе данных.
В одной из задач возникла необходимость отобразить результирующий набор данных зеркально относительно диагонали. На практике мне не довелось сталкиваться со случаями, когда подобный разворот необходимо делать именно средствами SQL. Обычно это решается несколько проще уже на наборе данных, который был возвращён средствами SQL. Хотя, слышал, что есть любители задавать подобную задачку на собеседованиях...
На sql-ex используется СУБД MS SQL Server, работающая с расширенным вариантом SQL — Transact-SQL (T-SQL). Гугль на запрос «T-SQL повернуть таблицу» сразу подсказал, что существует инструкция pivot, а последующее изучение этой темы — unpivot.
Ужас! Таково было моё впечатление после изучения документации и примеров использования. Синтаксис специфический и интуитивно непонятный, документация и имеющиеся примеры из одной колонки делают одну строку и наоборот. А как быть с несколькими сроками и столбцами? В редких случаях имеем примеры нескольких строк и колонок в результате, но полученных агрегатными функциями. А что делать, если меня в конкретном случае агрегирование не интересует? «Ну нипанятна!»
Скажу откровенно, как работает pivot, к моменту написания этих строк я так до конца и не разобрался. Тем не менее, попробую в процессе написания понять сам и как можно проще объяснить читателям.
Формулируем задачу
В общем случае задача достаточно проста для понимания. Из определенного исходного набора данных получить результирующий, зеркально отображенный относительно диагонали, например:
Исходный набор Результирующий набор
------------------------------ ------------------------
Фамилия Г.р. Пол Рост Чел.1 Чел.2 Чел.3
------------------------------ ------------------------
Иванов 1972 м 176 Иванов Петров Сидорова
Петров 1981 м 181 1972 1981 1990
Сидорова 1990 ж 168 м м ж
176 181 168
Мы видим, что есть набор данных, в котором число колонок не совпадает с числом строк (4×3), при этом колонки превращаются в строки и наоборот (3×4).
Надеюсь, что к концу статьи мы сможем проделать подобную операцию. К сожалению, одной простой командой такая транспозиция не произойдет.
Примеры будут приводится по структуре и данным из тестовой БД сайта sql-ex (другой возможности проверить запросы на СУБД MS SQL Server у меня просто нет).
Начнём с конца, с освоения unpivot, он мне показался проще.
UNPIVOT
UNPIVOT используется совместно с инструкцией SELECT и позволяет строку с данными развернуть в виде колонки.
Например, есть таблица Product, содержащая информацию о производителе, номере и типе продукции:
Product ------------------ maker varchar(10) model varchar(50) type varchar(50)
При выполнении следующего простейшего запроса:
select maker, model, type from product where model = '1276'получим:
maker model type A 1276 Printer
Все же для начала начнём с одной строчки. Сразу можно догадаться, что развороту данных помешает различный тип данных полей. Поэтому предыдущий запрос слегка изменяется, поле maker приводится к типу остальных полей — cast(maker as varchar(50)). Теперь ничто не мешает совершить разворот:
select aData from (
-- Это предыдущий запрос с приведенным типом поля maker
select cast(maker as varchar(50)) maker, model, type
from product where model = '1276'
) as t
unpivot (
aData for fields in (maker, model, type)
) as unpvtВ результате выполнения этого, пока еще непонятного, запроса получим:
aData A 1276 Printer
Т. е. строка развернулась и стала вертикально. Имевшийся ранее селектовый запрос обёрнут новым, в котором присутствует блок unpivot. Синтаксис этого блока можно описать следующим образом:
unpivot(
<Поле1>
for <Поле2>
in (<ПереченьПолей>)
)где:
- <Поле1> — имя поля внешнего запроса, в которое попадут «повёрнутые» данные. В примере было задано имя поля aData и выведены его значения (
select aData from ...); - <Поле2> — имя поля, содержащего имена полей вложенного запроса. Это поле может быть выведено отдельной колонкой;
- <ПереченьПолей> — перечень имён полей вложенного запроса, выводимых в результирующую колонку. Здесь могут быть перечислены все или часть полей вложенного запроса, которые необходимо вывести в результирующую колонку.
Как можно догадаться, изменение первой строчки запроса
select fields, aData from ...даст следующий результат:
fields aData maker A model 1276 type Printer
Тот же результат можно получить запросом select * from ..., только первой будет выведена колонка aData.
Теперь рассмотрим работу с несколькими записями вложенного запроса. Для понимания вполне хватит двух. Изменим первичный запрос:
select cast(maker as varchar(50)) maker, model, type
from product where model in ('1276', '2113')Тестовые данные:
maker model type A 1276 Printer E 2113 PC
В поворачивающем запросе изменился только вложенный подзапрос t:
select aData from (
select cast(maker as varchar(50)) maker, model, type
from product where model in ('1276', '2113')
) as t
unpivot (
aData for fields in (maker, model, type)
) as unpvtВ результате его выполнения получим:
aData A 1276 Printer E 2113 PC
Явная бессмыслица — данные в таком виде вряд ли применимы (что получится, если добавить в конце запроса сортировку? Например: order by fields).
Чтобы данным придать осмысленность, попробуем их представить в таком виде:
model aData 1276 A 1276 Printer 2113 E 2113 PC
Здесь для каждой модели в отдельную колонку выведены характеристики «производитель» и «тип».
Если первую строку запроса изменить, добавив в нее поле model из вложенного запроса:
select model, aData from ...то при выполнении запроса возникнет ошибка — поле «модель» участвует в развороте данных и не может быть выведено в отдельную колонку. Что делать? Всего лишь исключить поле model из перечня в блоке unpivot. Получим следующий рабочий запрос:
select model, aData from (
select cast(maker as varchar(50)) maker, model, type
from product where model in ('1276', '2113')
) as t
unpivot (
aData for fields in (maker, type)
) as unpvtЕсли в первую строку запроса добавить еще поле fields и изменить имена полей, получим:
select model, fields, aData from (
select cast(maker as varchar(50)) as [производитель], model, type as [тип]
from product where model in ('1276', '2113')
) as t
unpivot (
aData for fields in ([производитель], [тип])
) as unpvtи результат запроса:
model fields aData 1276 производитель A 1276 тип Printer 2113 производитель E 2113 тип PC
Таким образом, UNPIVOT позволяет:
- полностью или частично развернуть запись (строку) и представить её в вертикальном, колоночном виде;
- вывести в отдельной колонке имена полей или их синонимы, заданные в запросе;
- выводить поля из внутреннего запроса в виде отдельной колонки с тем ограничением, что эти поля не должны участвовать в развороте данных (отсутствовать в перечислении полей в блоке unpivot).
PIVOT
PIVOT является обратным по отношению к UNPIVOT, т. е. столбцы поворачивает в строки, но делает это несколько иначе, с использованием агрегатной функции.
Синтаксис подобен:
pivot(
aggregation(<Поле1>)
for <Поле2>
in (<ПереченьЗначений>)
)Особенности синтаксиса:
- в отличие от unpivot, в котором <Поле1> было полем результирующего набора данных, здесь <Поле1> — имя поля, которое должно быть в исходном наборе;
- в обязательном порядке по полю <Поле1> должна выполняться агрегация (sum, count, avg и т. п.). Странная на первый взгляд особенность, её рассмотрим позднее;
- <Поле2> — имя поля исходного набора данных, значения которого будут выступать в роли колонок итогового набора данных — <ПереченьЗначений>, в эти поля будет выводится результат выполнения агрегатной функции.
Рассмотрим пример.
select * from product
pivot (
count(model) for maker in ([A], [B], [D])
) as pvtРезультат выполнения запроса:
type A B D Laptop 2 1 0 PC 2 1 0 Printer 3 0 2
Используемые в блоке pivot запроса поля model и maker, это поля, которые содержит исходный набор данных, таблица product. В перечислении maker in ([A], [B], [D]) значения «A», «B» и «D» — названия тех производителей из колонки maker, данные по которым необходимо вывести в виде отдельных колонок.
Здесь сразу заметно существенное ограничение в использовании pivot — с его помощью поворачивается не любой набор данных, а тот, из которого мы можем получить заранее оговорённый набор колонок (кварталы года, дни месяца, перечень конкретных компаний, сотрудников и т. п.).
При ближайшем рассмотрении тестового примера становится понятно, что в отличие от unpivot, осуществляющего «чистый» разворот, pivot в первую очередь предназначен для создания таких себе отчетов в более удобочитаемой форме, поэтому в нем и присутствует необходимость использования агрегатной функции.
В начале непонятно, откуда в примере взялась колонка type, если в запросе мы её нигде не использовали? Pivot делает отчет по тому набору данных, который ему передан. В примере он получил в качестве набора данных просто таблицу product, которая содержит три поля maker, model и type. Мы указали просчитать количество моделей по таким-то производителям, но поскольку в наборе данных есть еще поля, то pivot делает группировку и по всем остальным полям, оставляя их в итоговом наборе данных.
Т.е. с точки зрения полученных данных наш запрос полностью аналогичен такому:
select maker, type, count(model) models
from product
where maker in ('A', 'B', 'D')
group by maker, typeотличается лишь представление данных:
maker type models A Laptop 2 B Laptop 1 A PC 2 B PC 1 A Printer 3 D Printer 2
Как быть, если мы хотим выбрать итог по производителям без учёта типа продукции? Ограничить набор данных требуемыми полями. Получим:
select 'Число моделей:' as [Производитель:], * from (
select maker, model from Product
where maker in ('A', 'B', 'D')
) as pr
pivot (
count(model) for maker in ([A], [B], [D])
) as pvtЗаодно я сразу ограничил набор исходных данных, с которыми мы работаем (maker in ('A', 'B', 'D')). В результате получим:
Производитель: A B D Число моделей: 7 2 2
Для придания человекочитаемости результату запроса я нарушил принципы нормализации, в колонке «Производитель:» внесено значение «Число моделей:» :-)
Если не удовлетворяют имена колонок, полученные по значениям из исходного набора данных, то их можно переименовать обычным способом:
select [A] as 'Произв. 1', [B] as 'Произв. 2', [D] as 'Произв. 3' from...Таким образом, PIVOT позволяет:
- выполнить агрегатную функцию по полю исходного набора данных, при этом считается, что по остальным полям набора производится группировка;
- результаты выполнения агрегатной функции вывести в горизонтальном виде, при этом колонками для вывода служат заранее определенные (известные) значения одного из полей исходного набора данных. Остальные поля исходного набора данных могут быть выведены отдельными колонками, результаты агрегации выводятся не только в разных колонках, но и в разных строках (получается нечто похожее на сводный отчет, «шахматка»).
Полный разворот
Работу PIVOT и UNPIVOT рассмотрели, но первоначально задекларированную задачу зеркально отобразить таблицу ещё не решили. Этим и займёмся.
Допустим, что в интернет-магазине есть функционал, обеспечивающий возможность показывать сравнительную характеристику нескольких выбранных пользователем ПК, при этом, характеристики каждого ПК выводятся в виде отдельной колонки, а на каждый вид характеристики выделена отдельная строчка.
Например, пользователь выбрал для сравнения ПК с кодами товара 1, 2, 4 и 7.
Обычный запрос
select * from pc where code in (1,2,4,7)выдаст следующий набор данных:
code model speed ram hd cd price 1 1232 500 64 5.0 12x 600.0000 2 1121 750 128 14.0 40x 850.0000 4 1121 600 128 14.0 40x 850.0000 7 1232 500 32 10.0 12x 400.0000
Как можно заметить, существуют одинаковые модели, имеющие различные характеристики, поэтому сразу понятно, что для сравнения группировать характеристики следует не по модели, а по коду продукта.
Шаг 1. Вертикальное представление характеристик
На первом шаге получим характеристики в вертикальном представлении с использованием UNPIVOT.
select code, prop, val from (
select
code,
model,
cast(speed as varchar(50)) speed,
cast(ram as varchar(50)) ram,
cast(hd as varchar(50)) hd,
cast(cd as varchar(50)) cd,
cast(price as varchar(50)) price
from pc where code in (1,2,4,7)
) x
unpivot (
val for prop in (model, speed, ram, hd, cd, price)
) unpvtЗдесь все значения, попадающие в колонку характеристик, приведены к типу наибольшего из строковых полей model — varchar(50).
Чтобы значения можно было однозначно идентифицировать, выведены колонки с кодом продукта и названием характеристики.
При выполнении запроса получим следующие данные:
code prop val 1 model 1232 1 speed 500 1 ram 64 1 hd 5 1 cd 12x 1 price 600.00 2 model 1121 2 speed 750 2 ram 128 2 hd 14 2 cd 40x 2 price 850.00 4 model 1121 4 speed 600 4 ram 128 4 hd 14 4 cd 40x 4 price 850.00 7 model 1232 7 speed 500 7 ram 32 7 hd 10 7 cd 12x 7 price 400.00
Шаг 2. Сведение характеристик в строки
На предыдущем шаге характеристики ПК были представлены в вертикальном виде, но в одной колонке для всех ПК. Осталось характеристики каждого ПК вывести в отдельную колонку.
Это умеет делать PIVOT, но есть одна проблема: PIVOT — конструкция, использующая агрегацию. На sql-ex я подсмотрел достаточно простое решение: поскольку для пары code+prop в нашем наборе данных существует только одно значение val, то при группировке по этим полям выражение max(val) выдаст само это значение :-)
Запрос:
select * from (
select code, prop, val from (
select
code,
model,
cast(speed as varchar(50)) speed,
cast(ram as varchar(50)) ram,
cast(hd as varchar(50)) hd,
cast(cd as varchar(50)) cd,
cast(price as varchar(50)) price
from pc where code in (1,2,4,7)
) x
unpivot (
val for prop in (model, speed, ram, hd, cd, price)
) unpvt
) xx
pivot (
max(val) for code in ([1],[2],[4],[7])
) pvtВ результате получим:
prop 1 2 4 7 cd 12x 40x 40x 12x hd 5 14 14 10 model 1232 1121 1121 1232 price 600.00 850.00 850.00 400.00 ram 64 128 128 32 speed 500 750 600 500
Шаг 3. Улучшение визуального представления
Требуемые данные в нужной структуре представления получены. Что меня не устраивает — неинформативные заголовки колонок и неудобный для восприятия порядок сортировки характеристик. Сделаем незначительные доработки запроса:
select prop as 'Хар-ка', [1] as 'Код № 1', [2] as 'Код № 2', [4] as 'Код № 4', [7] as 'Код № 7'
from (
select code, prop, val from (
select
code,
model [0.model],
cast(speed as varchar(50)) [2.speed],
cast(ram as varchar(50)) [3.ram],
cast(hd as varchar(50)) [4.hd],
cast(cd as varchar(50)) [5.cd],
cast(price as varchar(50)) [1.price]
from pc where code in (1,2,4,7)
) x
unpivot (
val for prop in ([0.model], [2.speed], [3.ram], [4.hd], [5.cd], [1.price])
) unpvt
) xx
pivot (
max(val) for code in ([1],[2],[4],[7])
) pvt
order by propВ результате его выполнения получим:
Хар-ка Код № 1 Код № 2 Код № 4 Код № 7 0.model 1232 1121 1121 1232 1.price 600.00 850.00 850.00 400.00 2.speed 500 750 600 500 3.ram 64 128 128 32 4.hd 5 14 14 10 5.cd 12x 40x 40x 12x
Заключение
Инструкции PIVOT и UNPIVOT — достаточно мощный и интересный инструмент, расширяющий возможности SQL. Инструмент неидеальный, т.к. у него имеются определённые ограничения. Например, не представляется возможным повернуть набор данных, для которого значения ключевого поля и число записей (строк) заранее неизвестны. Но в случаях, подобных примеру «сравнительной характеристики», эти инструкции позволяют ещё на стороне сервера баз данных выполнить достаточно лаконичный запрос без значительных последующих преобразований данных на сервере приложений или клиенте.
