5-6 列数固定の行列変換

SQLパズル

hobbyテーブル
ID   Favorite   name
--   --------   -------
 1   Car        Skyline
 1   Food       Curry
 2   Car        March
 3   Car        Mini
 3   Sport      Tennis
 4   Food       Water
 4   Sport      Soccer
 4   Car        K-Truck

hobbyテーブルを、
ID1つに対し1行にまとめる(Favoriteは最大3つとする)

出力結果
ID   Favortite1  name1     Favorite2  name2     Favorite3  Name3
--   ----------  -------   ---------  -------   ---------  --------
 1   Car         Skyline   Food       Curry     null       null
 2   Car         March     null       null      null       null
 3   Car         Mini      Sport      Tennis    null       null
 4   Car         K-Truck   Food       water     Sport      Soccer

データ作成スクリプト

create table hobby(
ID       number(1),
Favorite varchar2(5),
name     varchar2(8));

insert into hobby(ID,Favorite,name) values(1,'Car','Skyline');
insert into hobby(ID,Favorite,name) values(1,'Food','Curry');
insert into hobby(ID,Favorite,name) values(2,'Car','March');
insert into hobby(ID,Favorite,name) values(3,'Car','Mini');
insert into hobby(ID,Favorite,name) values(3,'Sport','Tennis');
insert into hobby(ID,Favorite,name) values(4,'Food','Water');
insert into hobby(ID,Favorite,name) values(4,'Sport','Soccer');
insert into hobby(ID,Favorite,name) values(4,'Car','K-Truck');
commit;

SQL

--■■■nameとFavoriteを文字列結合して順位を決める方法■■■
select a.ID,
a.Favorite as Favorite1,a.name as name1,
b.Favorite as Favorite2,b.name as name2,
c.Favorite as Favorite3,c.name as name3
from
(select aa.ID,aa.Favorite,aa.name from hobby aa
  where (select count(*) from hobby bb
          where aa.ID=bb.ID
            and aa.Favorite || aa.name > bb.Favorite || bb.name) = 0) a,
(select aa.ID,aa.Favorite,aa.name from hobby aa
  where (select count(*) from hobby bb
          where aa.ID=bb.ID
            and aa.Favorite || aa.name > bb.Favorite || bb.name) = 1) b,
(select aa.ID,aa.Favorite,aa.name from hobby aa
  where (select count(*) from hobby bb
          where aa.ID=bb.ID
            and aa.Favorite || aa.name > bb.Favorite || bb.name) = 2) c
where a.ID= b.ID(+)
  and a.ID= c.ID(+)
order by a.ID;

--■■■RowIDで順位を決める方法■■■
select a.ID,
a.Favorite as Favorite1,a.name as name1,
b.Favorite as Favorite2,b.name as name2,
c.Favorite as Favorite3,c.name as name3
from
(select aa.ID,aa.Favorite,aa.name from hobby aa
  where (select count(*) from hobby bb where aa.ID=bb.ID
                                         and aa.RowID < bb.RowID) = 0) a,
(select aa.ID,aa.Favorite,aa.name from hobby aa
  where (select count(*) from hobby bb where aa.ID=bb.ID
                                         and aa.RowID < bb.RowID) = 1) b,
(select aa.ID,aa.Favorite,aa.name from hobby aa
  where (select count(*) from hobby bb where aa.ID=bb.ID
                                         and aa.RowID < bb.RowID) = 2) c
where a.ID= b.ID(+)
  and a.ID= c.ID(+)
order by a.ID;

--■■■分析関数で順位を決める方法1■■■
select a.ID,
a.Favorite as Favorite1,a.name as name1,
b.Favorite as Favorite2,b.name as name2,
c.Favorite as Favorite3,c.name as name3
from
(select d.ID,d.Favorite,d.name,
 Row_Number() over(partition by d.ID order by d.Favorite,d.name) as Rank
   from hobby d) a,
(select d.ID,d.Favorite,d.name,
 Row_Number() over(partition by d.ID order by d.Favorite,d.name) as Rank
   from hobby d) b,
(select d.ID,d.Favorite,d.name,
 Row_Number() over(partition by d.ID order by d.Favorite,d.name) as Rank
   from hobby d) c
where a.ID=b.ID(+)
  and a.ID=c.ID(+)
  and a.Rank=1
  and b.Rank(+) = 2
  and c.Rank(+) = 3
order by a.ID;

--■■■分析関数で順位を決める方法2■■■
with WorkView as (
select ID,Favorite,name,
Row_Number() over(partition by ID order by Favorite,name) as Rank
from hobby)
select a.ID,
a.Favorite as Favorite1,a.name as name1,
b.Favorite as Favorite2,b.name as name2,
c.Favorite as Favorite3,c.name as name3
from WorkView a,WorkView b,WorkView c
where a.ID=b.ID(+)
  and a.ID=c.ID(+)
  and a.Rank= 1
  and b.Rank(+) = 2
  and c.Rank(+) = 3
order by a.ID;

--■■■分析関数で順位を決める方法3■■■
select ID,
max(decode(Rn,1,Favorite)) as Favorite1,
max(decode(Rn,1,name))     as name1,
max(decode(Rn,2,Favorite)) as Favorite2,
max(decode(Rn,2,name))     as name2,
max(decode(Rn,3,Favorite)) as Favorite3,
max(decode(Rn,3,name))     as name3
from (select ID,Favorite,name,
      Row_Number() over(partition by ID order by Favorite,name) as Rn
        from hobby)
group by ID
order by ID;

解説

select句の最大数が固定の場合は、SQLで行列変換できます。

同一IDの中での順位を特定する列がなくても、RowIDを比較して順位を決めることが出来ます。
Favoriteとnameを文字列結合して順位を決める方法もあります。

9-1  複数行を1行に変換

---