Версия 22:53, 14 мая 2010

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