7-76 グループごとの縦横変換

SQLパズル

CodeTable
Code  SubCode  SortKey
----  -------  -------
 001      AAA       10
 001      AAA       20
 001      BBB       50
 001      CCC       10
 002      AAA       30
 002      BBB       60
 003      BBB       10
 003      CCC       70

Codeごとに、SortKeyの昇順で、以下の
縦横変換を行う。

出力結果
Code  AAA   BBB   CCC
----  ----  ----  ----
 001    10    50    10
 001    20  null  null
 002    30    60  null
 003  null    10    70

データ作成スクリプト

create table CodeTable(
Code    char(3),
SubCode char(3),
SortKey number(2));

insert into CodeTable values('001','AAA',10);
insert into CodeTable values('001','AAA',20);
insert into CodeTable values('001','BBB',50);
insert into CodeTable values('001','CCC',10);
insert into CodeTable values('002','AAA',30);
insert into CodeTable values('002','BBB',60);
insert into CodeTable values('003','BBB',10);
insert into CodeTable values('003','CCC',70);
commit;

SQL

select Code,
max(decode(SubCode,'AAA',SortKey)) as AAA,
max(decode(SubCode,'BBB',SortKey)) as BBB,
max(decode(SubCode,'CCC',SortKey)) as CCC
from (select Code,SubCode,SortKey,
      Row_Number() over(partition by Code,SubCode order by SortKey) as Rank
      from CodeTable)
group by Code,Rank
order by Code,Rank;

解説

インラインビュー内でRow_Number関数で順位を求め、
集約させてます。

---