Участник:ArmorAdmin/PIVOT
Содержание
PIVOT — поворот таблицы в T-SQL
- Автор(ы): Чобиток Василий, 14 мая 2010
- В этой статье: о применении инструкций PIVOT и UNPIVOT языка T-SQL для разворота табличных данных.
Лирическое вступление
Решив возобновить свои навыки владения SQL, продолжил начатое когда-то решение упражнений на замечательном сайте [1]. В отличие от многих других ресурсов, где проводится тестирование путем выбора варианта среди ответов на вопрос, здесь решением является собственный 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
Новый поворачивающий запрос:
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).