loading..
Русский    English
01:11
листать

Оператор UNPIVOT

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

Консоль
Выполнить
  1. SELECT screen, AVG(price) avg_
  2. FROM Laptop
  3. GROUP BY screen;

screen avg_
11 700.00
12 960.00
14 1175.00
15 1050.00

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

avg_ 11 12 14 15
average price 700.00 960.00 1175.00 1050.00

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

Консоль
Выполнить
  1. SELECT screen -- заголовок столбца, который будет содержать заголовки
  2. -- строк исходной таблицы
  3. ,avg__ AS avg_
  4. -- заголовок столбца, который будет содержать значения из строки исходной таблицы
  5. FROM( -- pivot-запрос из предыдущего примера
  6. SELECT [avg_], [11],[12],[14],[15]
  7. FROM (SELECT 'average price' AS 'avg_', screen, price
  8. FROM Laptop) x
  9. PIVOT (AVG(price) FOR screen IN([11],[12],[14],[15]) )
  10. pvt
  11. -- конец pivot-запроса
  12. ) pvt
  13. UNPIVOT (avg__ -- заголовок столбца, который будет содержать значения
  14. -- из столбцов исходной таблицы, перечисленных ниже
  15. FOR screen IN([11],[12],[14],[15])
  16. ) unpvt;

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

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

trip_no spec info
1100 id_comp 4
1100 plane Boeing
1100 town_from Rostov
1100 town_to Paris
1100 time_out 14:30:00
1100 time_in 17:50:00

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

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

Консоль
Выполнить
  1. SELECT trip_no,CAST(id_comp AS CHAR(25)) id_comp,
  2. CAST(plane AS CHAR(25)) plane,town_from,town_to,
  3. CONVERT(CHAR(25),time_out, 108) time_out,
  4. CONVERT(CHAR(25),time_in,108) time_in
  5. FROM Trip WHERE trip_no =1100;


trip_no id_comp plane town_from town_to time_out time_in
1100 4 Boeing Rostov Paris 14:30:00 17:50:00

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

  1. CONVERT(CHAR(25),time_out, 108)

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

Консоль
Выполнить
  1. SELECT trip_no, spec, info FROM (
  2. SELECT trip_no,CAST(id_comp AS CHAR(25)) id_comp,
  3. CAST(plane AS CHAR(25)) plane,
  4. CAST(town_from AS CHAR(25)) town_from,
  5. CAST(town_to AS CHAR(25)) town_to,
  6. CONVERT(CHAR(25),time_out, 108) time_out,
  7. CONVERT(CHAR(25),time_in,108) time_in
  8. FROM Trip
  9. WHERE trip_no =1100 ) x
  10. UNPIVOT( info
  11. FOR spec IN(id_comp, plane, town_from, town_to, time_out, time_in)
  12. ) unpvt;

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

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

Bookmark and Share
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE CONSTRAINT Convert COUNT CROSS APPLY CTE DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FOREIGN KEY FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INNER JOIN insert INTERSECT IS NOT NULL IS NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker MAX Больше тэгов
Учебник обновлялся
вчера
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.
Rambler's Top100