10-143 Row_Number関数との差でグループ化

SQLパズル

TreeTable1
gcode  lvl  name
-----  ---  ----
   10    1    A
   21    2    B
   32    3    C
   40    4    D
   55    4    E
   60    3    F
   75    4    G
   80    3    H
   90    4    I
  101    3    J
  110    1    K
  122    2    L
  130    3    M
  144    4    N
  155    4    O
  166    3    P
  170    4    Q

lvlが、2以上インクリメントされることは、
ないものとして

gcodeの昇順で、以下の出力を行う

出力結果
L1  L2  L3  L4
--  --  --  --
A   B   C   D
-   -   -   E
-   -   F   G
-   -   H   I
-   -   -   J
K   L   M   N
-   -   -   O
-   -   P   Q

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table TreeTable1 as
select 10 as gcode,1 as lvl,'A' as coa_name from dual
union all select  21,2,'B' from dual
union all select  32,3,'C' from dual
union all select  40,4,'D' from dual
union all select  55,4,'E' from dual
union all select  60,3,'F' from dual
union all select  75,4,'G' from dual
union all select  80,3,'H' from dual
union all select  90,4,'I' from dual
union all select 101,3,'J' from dual
union all select 110,1,'K' from dual
union all select 122,2,'L' from dual
union all select 130,3,'M' from dual
union all select 144,4,'N' from dual
union all select 155,4,'O' from dual
union all select 166,3,'P' from dual
union all select 170,4,'Q' from dual;

SQL

select
nvl(max(decode(lvl,1,coa_name)),'-') as L1,
nvl(max(decode(lvl,2,coa_name)),'-') as L2,
nvl(max(decode(lvl,3,coa_name)),'-') as L3,
nvl(max(decode(lvl,4,coa_name)),'-') as L4
  from (select lvl,coa_name,
        Row_Number() over(order by gcode)-lvl as groupCD
          from TreeTable1)
group by groupCD
order by groupCD;

解説

lvlが、2以上インクリメントされることはないので、
Row_Number関数との差でグループ化してます

旅人算の感覚を使ってます。

---