Участник: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
