トップページに戻る
次のSQLパズルへ
前のSQLパズルへ
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のためのグループ化に使ってます。