CLERKS ANALYSTS MGRS PREZ SALES

SMITH SCOTT JONES KING WARD

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

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

select rn,

case when job=CLERK

then ename else null end as clerks,

case when job=ANALYST

then ename else null end as analysts,

case when job=MANAGER

then ename else null end as mgrs,

case when job=PRESIDENT

then ename else null end as prez,

case when job=SALESMAN

then ename else null end as sales

from ( select job, ename,

row number()over(partition by job order by ename) rn from emp ) x

RN CLERKS ANALYSTS MGRS PREZ SALES

1 FORD

2 SCOTT

1 ADAMS

2 JAMES

3 MILLER

4 SMITH

1 BLAKE

2 CLARK

3 JONES

1 KING

1 ALLEN

2 MARTIN

3 TURNER

4 WARD

На данный момент строки транспонированы в столбцы, осталось лишь удалить значения NULL, чтобы сделать результирующее множество более удобным для восприятия. Удаляем значения NULL с помощью агрегатной функции MAX и группируем результаты по RN. (Можно исполь-

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

select max(case when job=CLERK

then ename else null end) as clerks,

max(case when job=ANALYST

then ename else null end) as analysts,

max(case when job=MANAGER

then ename else null end) as mgrs,

max(case when job=PRESIDENT

then ename else null end) as prez,

max(case when job=SALESMAN

then ename else null end) as sales

from ( select job, ename,

row number()over(partition by job order by ename) rn from emp ) x group by rn

CLERKS ANALYSTS MGRS PREZ SALES

MILLER FORD CLARK KING TURNER

JAMES SCOTT BLAKE MARTIN ADAMS JONES WARD

SMITH ALLEN

Методика использования ROW NUMBER OVER для создания уникальных сочетаний строк исключительно полезна для форматирования результатов запросов. Рассмотрим запрос, создающий разреженный отчет, в котором служащие распределены по DEPTNO и JOB:

max(case when job=SALESMAN

then ename else null end) as sales

from ( select deptno,

job,

ename,

row number()over(partition by job order by ename) rn job, row number()over(partition by job order by ename) rn deptno from emp ) x

group by deptno, job, rn deptno, rn job order by 1

KING

ALLEN MARTIN TURNER WARD

Просто меняя значения, по которым происходит группировка (следовательно, и не участвующие в агрегации элементы списка SELECT), можно создавать отчеты разных форматов. Стоит потратить немного времени и поэкспериментировать, изменяя эти значения, чтобы понять, как меняются форматы в зависимости от того, что входит в конструкцию GROUP BY.

PostgreSQL и MySQL

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

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

ENAME