8-38 Row_Numberを求めてPivot

SQLパズル

ResTable      AppsTable
DNo  Ename    DNo  Ename
---  -----    ---  -----
100  AAA-1    400  400-1
100  AAA-2    400  400-2
100  AAA-3    400  400-3
200  200-1    400  400-4
200  200-2    400  400-5
200  200-3    500  500-1
200  200-4    500  500-2
300  300-1    600  600-1
300  300-2    600  600-2
300  300-3    600  600-3
              600  600-4
              700  700-1
              700  700-2
              700  700-3

下記の結果を取得する。

出力結果
Res          Apps
---------    ---------
100          400
    AAA-1        400-1
    AAA-2        400-2
    AAA-3        400-3
200              400-4
    200-1        400-5
    200-2    500
    200-3        500-1
    200-4        500-2
300          600
    300-1        600-1
    300-2        600-2
    300-3        600-3
null             600-4
null         700
null             700-1
null             700-2
null             700-3

SQLクックブックのレシピ14.9を参考にさせていただきました

データ作成スクリプト

create table ResTable(DNo,Ename) as
select 100,'AAA-1' from dual union
select 100,'AAA-2' from dual union
select 100,'AAA-3' from dual union
select 200,'200-1' from dual union
select 200,'200-2' from dual union
select 200,'200-3' from dual union
select 200,'200-4' from dual union
select 300,'300-1' from dual union
select 300,'300-2' from dual union
select 300,'300-3' from dual;

create table AppsTable(DNo,Ename) as
select 400,'400-1' from dual union
select 400,'400-2' from dual union
select 400,'400-3' from dual union
select 400,'400-4' from dual union
select 400,'400-5' from dual union
select 500,'500-1' from dual union
select 500,'500-2' from dual union
select 600,'600-1' from dual union
select 600,'600-2' from dual union
select 600,'600-3' from dual union
select 600,'600-4' from dual union
select 700,'700-1' from dual union
select 700,'700-2' from dual union
select 700,'700-3' from dual;

SQL

col res  for a10
col Apps for a10

select max(decode(ID,1,str)) as Res,
       max(decode(ID,2,str)) as Apps
from (select 1 as ID,Row_Number() over(order by DNo,Ename nulls first) as Rn,
      case grouping(Ename) when 1 then to_char(DNo)
                           else '    ' || Ename end as str
        from ResTable
      group by grouping sets((DNo,Ename),(DNo))
      union all
      select 2,Row_Number() over(order by DNo,Ename nulls first),
      case grouping(Ename) when 1 then to_char(DNo)
                           else '    ' || Ename end
        from AppsTable
      group by grouping sets((DNo,Ename),(DNo)))
group by Rn
order by Rn;

解説

Row_Numberを求めて、Pivotのためのグループ化に使ってます。

---