Версия 23:30, 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

Сразу можно догадаться, что развороту данных может помешать различный тип данных полей. В самом деле, не можем же мы в одной колонке выводить данные разного типа. Поэтому предыдущий запрос слегка изменяется, поле 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).