Присвоение каждому сочетанию JOB/ENAME уникального ранга делает каждую строку уникальной. Даже если есть служащие, имеющие одинаковые имена и занимающие одну должность, не будет двух служащих с одним рангом для данной должности. Этот шаг является самым важным при решении задачи. Если не выполнить этот первый шаг, внешний запрос в результате агрегации удалит нужные строки. Рассмотрим, как выглядело бы результирующее множество без присвоения ранга каждому сочетанию JOB/ENAME, если бы применялась техника, представленная в первом рецепте:

CLERKS ANALYSTS MGRS PREZ SALES

SMITH SCOTT JONES KING WARD

К сожалению, для каждого значения JOB возвращена только одна строка: служащий с наивысшим рангом. При разворачивании результирующего множества функции MIN или MAX должны использоваться только как средства для удаления значений NULL, без ограничения возвращаемых значений ENAME.

Теперь, когда ясен смысл назначения рангов, можно идти далее. Следующий шаг - использовать выражение CASE для распределения значений ENAME по соответствующим столбцам (JOB):

select rnk,

case when job=CLERK

from ( select e.job, e.ename,

(select count(*) from emp d where e.job=d.job and e.empno < d.empno) as rnk from emp e ) x

RNK CLERKS ANALYSTS MGRS PREZ SALES 3 SMITH

3 ALLEN

2 WARD

2 JONES

1 MARTIN

1 BLAKE

0 CLARK

1 SCOTT

0 KING

0 TURNER

2 ADAMS

1 JAMES

0 FORD 0 MILLER

На данный момент строки транспонированы в столбцы, осталось лишь удалить значения NULL, чтобы сделать результирующее множество более удобным для восприятия. Удаляем значения NULL с помощью агрегатной функции MAX и группируем результаты по RNK. (Выбор MAX произволен. Можно использовать и функцию MIN.) Сочетания значений RN/JOB/ENAME уникальны, поэтому агрегатная функция просто удалит значения NULL:

3 5 6

Оно должно быть преобразовано к такому виду:

DEPTNO COUNTS BY DEPT

10 3

20 5

30 6

Решение

Чтобы получить требуемое результирующее множество, можно просто выполнить операции COUNT и GROUP BY для таблицы EMP. Однако главное здесь - понимать, что данные не хранятся как строки; возможно, данные денормализованы и агрегированные значения хранятся как множество столбцов.

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

Мы не будем создавать денормализованную таблицу данных. В решении данного рецепта применим решение из первого рецепта главы и создадим широкое результирующее множество. Вот полное решение:

from ( select e.job, e.ename,

(select count(*) from emp d where e.job=d.job and e.empno < d.empno) as rnk from emp e ) x

group by rnk

CLERKS ANALYSTS MGRS PREZ SALES

MILLER FORD CLARK KING TURNER

JAMES SCOTT BLAKE MARTIN

ADAMS JONES WARD SMITH ALLEN

Обратное разворачивание результирующего множества

Задача

Требуется преобразовать столбцы в строки. Рассмотрим следующее результирующее множество:

DEPTNO 10 DEPTNO 20 DEPTNO 30