Новое в стандарте и реализациях языка SQL

Операторы PIVOT и UNPIVOT

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

Консоль

Выполнить

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

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;

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;

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;

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 содержит сами параметры. Результат выполнения запроса уже был представлен в условии задачи.