Оператор UNPIVOT

Как следует из названия оператора, UNPIVOT выполняет обратную по отношению к PIVOT операцию, т.е. представляет данные, записанные в строке таблицы, в одном столбце. В примере, рассмотренном в предыдущем параграфе, мы с помощью оператора PIVOT разворачивали в строку таблицу, полученную с помощью следующего запроса:

SELECT screen, AVG(price) avg_
FROM Laptop
GROUP BY screen;

В результате было получено следующее представление:

Исходный результат мы можем получить, если применим к pivot-запросу unpivot-преобразование:

SELECT screen -- заголовок столбца, который будет содержать заголовки
              -- строк исходной таблицы
      ,avg__ AS avg_
              -- заголовок столбца, который будет содержать значения из строки исходной таблицы
FROM ( -- pivot-запрос из предыдущего примера
      SELECT [avg_],
             [11],[12],[14],[15]
      FROM (SELECT 'average price' AS 'avg_', screen, price
            FROM Laptop
           ) x
      PIVOT
        (AVG(price)
         FOR screen
         IN([11],[12],[14],[15])
        ) pvt
      -- конец pivot-запроса
     ) pvt
UNPIVOT
    (avg__ -- заголовок столбца, который будет содержать значения
           -- из столбцов исходной таблицы, перечисленных ниже
     FOR screen in([11],[12],[14],[15])
    ) unpvt;

Заметим, что имя avg_ нельзя использовать в операторе UNPIVOT, поскольку оно уже использовалось в операторе PIVOT, поэтому я использовал новое имя avg__, которому затем присвоил алиас, чтобы полностью воссоздать результат, полученный с помощью группировки.

Рассмотрим теперь более содержательный пример. Пусть требуется информацию о рейсе 1100 представить в следующем виде:

Поскольку информация из строки таблицы трансформируется в столбец, то напрашивается использование оператора UNPIVOT. Здесь следует сделать одно замечание. Значения в этом столбце должны быть одного типа. Поскольку в этот столбец в нашем примере собираются значения из разных столбцов исходной таблицы, то нужно преобразовать их к единому типу. Более того, должны совпадать не только типы, но и размер.

Общим типом в нашем случае является строковый тип. Поскольку столбцы town_from и town_to уже имеют тип char(25), то приведем все к этому типу:

SELECT trip_no,CAST(id_comp AS CHAR(25)) id_comp,
       CAST(plane AS CHAR(25)) plane,
       town_from,
       town_to,
       CONVERT(CHAR(25),time_out, 108) time_out,
       CONVERT(CHAR(25),time_in,108) time_in
FROM Trip
WHERE trip_no =1100;

Здесь мы заодно преобразовали время вылета/прилета, убрав из него составляющую даты:

CONVERT(CHAR(25),time_out, 108)

Остальное, я надеюсь, понятно из кода:

SELECT trip_no, spec, info 
FROM (SELECT trip_no,CAST(id_comp AS CHAR(25)) id_comp,
             CAST(plane AS CHAR(25)) plane,
             CAST(town_from AS CHAR(25)) town_from,
             CAST(town_to AS CHAR(25)) town_to,
             CONVERT(CHAR(25),time_out, 108) time_out,
             CONVERT(CHAR(25),time_in,108) time_in
      FROM Trip
      WHERE trip_no =1100
     ) x
UNPIVOT(info
        FOR spec IN(id_comp, plane, town_from, town_to, time_out, time_in)
       ) unpvt;

Столбец с именем spec используется для вывода названий параметров, а столбец info содержит сами параметры. Результат выполнения запроса уже был представлен в условии задачи.

Рекомендуемые упражнения: 146, 97

Пусть нам требуется повернуть строку, содержащую NULL-значение в одном из столбцов.

WITH utest AS
(SELECT 1 a, 2 b, NULL c)
SELECT * FROM utest;

Т.е. вместо результата

мы хотим получить

Применим оператор UNPIVOT:

WITH utest AS
(SELECT 1 a, 2 b, NULL c)
SELECT col, value 
FROM utest
UNPIVOT (
         value FOR col IN (a,b,c)
        ) AS unpvt;

Первая неожиданность - ошибка компиляции:

Это означает, что сервер неявно не преобразовал тип столбца “с”, содержащий NULL, к типу первых двух столбцов, которые могут быть оценены как целочисленные.

Давайте сделаем это явно:

WITH utest AS
(SELECT 1 a, 2 b, CAST(NULL AS INT) c)
SELECT col,value 
FROM utest
UNPIVOT (
         value FOR col IN (a,b,c)
        ) AS unpvt;

Вторая неожиданность - оказывается UNPIVOT игнорирует NULL-значения, не выводя их в результирующем наборе.

Первое, что приходит в голову всем, это заменить NULL каким-нибудь валидным значением, заведомо отсутствующим в столбце. Если, в соответствии с ограничениями предметной области, значения с не могут быть отрицательными, заменим NULL на -1:

WITH utest AS
(SELECT 1 a, 2 b, COALESCE(CAST(NULL AS INT),-1) c)
SELECT col, value 
FROM utest
UNPIVOT (
         value FOR col IN (a, b, c)
        ) AS unpvt;

Остался последний шаг, о котором многие забывают, решая задачи на сайте sql-ex.ru. А именно, обратное преобразование. Вместо этого пытаются подобрать такое значение, которое позволило бы “удовлетворить” систему проверки. Иногда это получается, например, если сравнение NULL и ’’ (пустой строки) оценивается на сайте как true. Но понятно, что на это полагаться не стоит. Итак, обратное пребразование:

WITH utest AS
(SELECT 1 a, 2 b, COALESCE(CAST(NULL AS INT),-1) c)
SELECT col, NULLIF(value, -1) value 
FROM utest
UNPIVOT (
         value FOR col IN (a, b, c)
        ) AS unpvt;

Здесь как нельзя более кстати пришлась функция NULLIF.