Поскольку в качестве вложенного запроса DRIVER может использоваться практически любое корректное табличное выражение, можно выполнить объединение таблицы EMP с таблицей DEPT и затем с помощью PIVOT получить эти строки. В результате выполнения следующего запроса будет получено необходимое результирующее множество:

select [ACCOUNTING] as ACCOUNTING,

[SALES] as SALES, [RESEARCH] as RESEARCH, [OPERATIONS] as OPERATIONS

from (

select d.dname, e.empno from emp e,dept d where e.deptno=d.deptno

) driver pivot (

count(driver.empno)

for driver.dname in ([ACCOUNTING],[SALES],[RESEARCH],[OPERATIONS]) ) as empPivot

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

Обратное разворачивание отчета с помощью оператора SQL Server UNPIVOT

Задача

Имеется результирующее множество, полученное с применением разворачивания (или просто массивная таблица), и требуется нормализовать (выполнить обратное разворачивание) результирующее множест-

таким образом, чтобы в качестве имени столбца использовалось реальное название отдела. Ниже приведены строки таблицы DEPT:

select * from dept

DEPTNO DNAME LOC

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS 30 SALES CHICAGO

40 OPERATIONS BOSTON

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

ACCOUNTING RESEARCH SALES OPERATIONS

во. Например, вместо результирующего множества, состоящего из одной строки и четырех столбцов, необходимо получить результирующее множество с двумя столбцами и четырьмя строками. Используя результирующее множество предыдущего рецепта, мы должны преобразовать такую таблицу:

ACCOUNTING RESEARCH

SALES OPERATIONS

в следующую:

DNAME

ACCOUNTING

RESEARCH

SALES

OPERATIONS

Решение

Ведь вы не думали, что SQL Server может предоставить возможность развернуть таблицу без возможности развернуть ее в обратном направлении? Чтобы провести обратное разворачивание результирующего множества, просто используем его как driver и позволим оператору UNPIVOT выполнить всю работу. Необходимо только задать имена столбцов:

select DNAME, CNT

from (

select [ACCOUNTING] as ACCOUNTING,

[SALES] as SALES, [RESEARCH] as RESEARCH, [OPERATIONS] as OPERATIONS

from (

select d.dname, e.empno

from emp e,dept d

where e.deptno=d.deptno

) driver pivot ( count(driver.empno) for driver.dname in ([ACCOUNTING],[SALES],[RESEARCH],[OPERATIONS])

16 ) as empPivot

17 ) new driver

18 unpivot (cnt for dname in (ACCOUNTING,SALES,RESEARCH,OPERATIONS)

19 ) as un pivot

Надеюсь, прежде чем прочитать этот рецепт, вы ознакомились с предыдущим, потому что вложенный запрос NEW DRIVER взят оттуда без всяких изменений (если возникают какие-то вопросы, пожалуйста, рассмотрите сначала предыдущий рецепт, а потом переходите

к данному). Поскольку строки 3-16 повторяют код, который мы уже видели, единственное новшество здесь - строка 18, в которой используется оператор UNPIVOT.

Команда UNPIVOT просто просматривает результирующее множество, возвращаемое NEW DRIVER, и обрабатывает каждый столбец и строку. Например, оператор UNPIVOT обрабатывает имена столбцов, возвращаемые NEW DRIVER. Встречая имя ACCOUNTING, он преобразует имя столбца ACCOUNTING в значение строки (поля DNAME). Он также берет возвращаемое NEW DRIVER значение ACCOUNTING (которое равно 3) и возвращает его тоже как часть строки ACCOUNTING (значение столбца CNT). UNPIVOT делает таким образом для каждого из элементов, определенных в списке FOR, и просто возвращает каждый из них как строку.

Новое результирующее множество компактно и состоит всего из двух столбцов, DNAME и CNT, и четырех строк:

select DNAME, CNT from (

select [ACCOUNTING] as ACCOUNTING,

[SALES] as SALES,

[RESEARCH] as RESEARCH,

[OPERATIONS] as OPERATIONS

from (

select d.dname, e.empno

from emp e,dept d

where e.deptno=d.deptno

) driver pivot ( count(driver.empno)

for driver.dname in ( [ACCOUNTING],[SALES],[RESEARCH],[OPERATIONS] )

) as empPivot ) new driver

unpivot (cnt for dname in (ACCOUNTING,SALES,RESEARCH,OPERATIONS)

) as un pivot

DNAME CNT

ACCOUNTING 3

RESEARCH 5

SALES b

OPERATIONS 0

Транспонирование результирующего множества с помощью оператора Oracle MODEL

Задача

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