Представьте, что все это значит для производительности, когда можно создать такой отчет лишь за одно обращение к таблице. Просто замечательно.

Как добавить заголовок столбца в дважды развернутое результирующeе множество

Задача

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

select * from it research

DEPTNO ENAME

100 HOPKINS

100 JONES

100 TONEY

200 MORALES

200 P.WHITAKER

200 MARCIANO

200 ROBINSON

300 LACY

300 WRIGHT

300 J.TAYLOR

select * from it apps

DEPTNO ENAME

400 CORRALES 400 MAYWEATHER 400 CASTILLO

400 MARQUEZ

400 MOSLEY 500 GATTI 500 CALZAGHE 600 LAMOTTA 600 HAGLER 600 HEARNS 600 FRAZIER 700 GUINN 700 JUDAH 700 MARGARITO

Хотелось бы создать отчет, в котором все служащие из этих таблиц были бы перечислены в двух столбцах. Необходимо представить все отделы

(DEPTNO) и всех служащих (ENAME) для каждого из них. В итоге должно быть получено следующее результирующее множество:

RESEARCH APPS

100 400 JONES MAYWEATHER

TONEY CASTILLO

HOPKINS MARQUEZ

200 MOSLEY

P.WHITAKER CORRALES

MARCIANO 500

ROBINSON CALZAGHE

MORALES GATTI

300 600

WRIGHT HAGLER

J.TAYLOR HEARNS

LACY FRAZIER

LAMOTTA 700

JUDAH

MARGARITO

GUINN

Решение

В целом для данного решения не требуется ничего, кроме простого составления данных и разворачивания (объединения, затем разворачивания) с дополнительным изменением порядка: DEPTNO должно предшествовать ENAME. В приводимой здесь технике формирование дополнительных строк для каждого DEPTNO осуществляется с помощью декартова произведения. В результате мы получаем строки, необходимые для представления всех служащих, плюс место для вывода DEPTNO. Решение использует синтаксис Oracle, но поскольку DB2 поддерживает оконные функции, которые могут работать со скользящими окнами данных (оператор кадрирования), это решение нетрудно изменить для работы с DB2. Таблицы ITRESEARCH и ITAPPS используются только в данном рецепте, поэтому выражения создания этих таблиц показаны вместе с решением:

create table IT research (deptno number, ename varchar2(20))

insert into IT research values (100,HOPKINS)

insert into IT research values (100,JONES)

insert into IT research values (100,TONEY)

insert into IT research values (200,MORALES)

insert into IT research values (200,P.WHITAKER)

insert into IT research values (200,MARCIANO)

insert into IT research values (200,ROBINSON)

insert into IT research values (300,LACY)

insert into IT research values (300,WRIGHT)

insert into IT research values (300,J.TAYLOR)

1 select max(decode(flag2,0,it dept)) research,

2 max(decode(flag2,1,it dept)) apps

3 from (

4 select sum(flag1)over(partition by flag2

5 order by flag1,rownum) flag,

6 it dept, flag2

7 from (

8 select 1 flag1, 0 flag2,

9 decode(rn,1,to char(deptno), ename) it dept

10 from (

11 select x.*, y.id,

12 row number()over(partition by x.deptno order by y.id) rn

13 from (

14 select deptno,

15 ename,

16 count(*)over(partition by deptno) cnt

17 from it research

18 ) x,

19 (select level id from dual connect by level <= 2) y

20 )

21 where rn <= cnt+1

22 union all

23 select 1 flag1, 1 flag2,

24 decode(rn,1,to char(deptno), ename) it dept

25 from (

26 select x.*, y.id,

27 row number()over(partition by x.deptno order by y.id) rn

28 from (

29 select deptno,

30 ename,

31 count(*)over(partition by deptno) cnt

32 from it apps

33 ) x,