10-15 キーごとに行列変換

SQLパズル

五十音テーブル
KEY  No  Cr
---  --  --
  1  01  い
  1  02  ろ
  1  03  は
  1  04  に
  1  05  ほ
  1  06  へ
  1  07  と
  2  01  あ
  2  02  い
  2  03  う
  2  04  え
  2  05  お
  2  06  か

五十音テーブルをKEYごとに、6行を1行にまとめて出力する。

出力結果
KEY   No1   Cr1   No2   Cr2   No3   Cr3   No4   Cr4   No5   Cr5   No6   Cr6
---  ----  ----  ----  ----  ----  ----  ----  ----  ----  ----  ----  ----
  1    01    い    02    ろ    03    は    04    に    05    ほ    06    へ
  1    07    と  null  null  null  null  null  null  null  null  null  null
  2    01    あ    02    い    03    う    04    え    05    お    06    か

データ作成スクリプト

create table 五十音(
KEY number(1),
No  char(2),
Cr  char(2));

insert into 五十音 values(1,'01','い');
insert into 五十音 values(1,'02','ろ');
insert into 五十音 values(1,'03','は');
insert into 五十音 values(1,'04','に');
insert into 五十音 values(1,'05','ほ');
insert into 五十音 values(1,'06','へ');
insert into 五十音 values(1,'07','と');
insert into 五十音 values(2,'01','あ');
insert into 五十音 values(2,'02','い');
insert into 五十音 values(2,'03','う');
insert into 五十音 values(2,'04','え');
insert into 五十音 values(2,'05','お');
insert into 五十音 values(2,'06','か');
commit;

SQL

--■■■グループ化しない方法■■■
select KEY,
No1,Cr1,
No2,Cr2,
No3,Cr3,
No4,Cr4,
No5,Cr5,
No6,Cr6
from (select Row_Number() over(partition by KEY order by No) as Rank,
      KEY,
      No as No1,
      Cr as Cr1,
      Lead(No,1) over(partition by KEY order by No) as No2,
      Lead(Cr,1) over(partition by KEY order by No) as Cr2,
      Lead(No,2) over(partition by KEY order by No) as No3,
      Lead(Cr,2) over(partition by KEY order by No) as Cr3,
      Lead(No,3) over(partition by KEY order by No) as No4,
      Lead(Cr,3) over(partition by KEY order by No) as Cr4,
      Lead(No,4) over(partition by KEY order by No) as No5,
      Lead(Cr,4) over(partition by KEY order by No) as Cr5,
      Lead(No,5) over(partition by KEY order by No) as No6,
      Lead(Cr,5) over(partition by KEY order by No) as Cr6
      from 五十音)
where mod(Rank,6)=1
order by KEY,No1;

--■■■グループ化する方法■■■
select Key,
max(decode(mod(rn,6),1,Cr)) as Cr1,max(decode(mod(rn,6),1,No)) as No1,
max(decode(mod(rn,6),2,Cr)) as Cr2,max(decode(mod(rn,6),2,No)) as No2,
max(decode(mod(rn,6),3,Cr)) as Cr3,max(decode(mod(rn,6),3,No)) as No3,
max(decode(mod(rn,6),4,Cr)) as Cr4,max(decode(mod(rn,6),4,No)) as No4,
max(decode(mod(rn,6),5,Cr)) as Cr5,max(decode(mod(rn,6),5,No)) as No5,
max(decode(mod(rn,6),0,Cr)) as Cr6,max(decode(mod(rn,6),0,No)) as No6
from (select KEY,No,Cr,Row_Number() over(partition by KEY order by No) as rn
        from 五十音)
group by Key,trunc((rn-1)/6)
order by Key,trunc((rn-1)/6);

解説

Lead関数で、ソートした時の後続の行の列の値を取得してます。
グループ化してもいいでしょう。

---