Оператор 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;

 | col | value | |——-|———| | a | 1 | | b | 2 | | c | -1 |

Остался последний шаг, о котором многие забывают, решая задачи на сайте 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.