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

SQLパズル

Namesテーブル
name
-------
Al
Ben
Charlie
David
Ed
Frank
Greg
Howard
Ida
Joe
Ken
Larry
Mike

Namesテーブルのname列を
5行ごとに、1行で表示する(最終行の余った列は、nullとする)
出力順は、行も列も、Namesテーブルのname列の昇順とする。

出力例(2行を1行に変換)
name1    name2
-------  ------
Al       Ben
Charlie  David
Ed       Frank
Greg     Howard
Ida      Joe
Ken      Larry
Mike     null

出力例(3行を1行に変換)
name1  name2   name3
-----  ------  -------
Al     Ben     Charlie
David  Ed      Frank
Greg   Howard  Ida
Joe    Ken     Larry
Mike   null    null

出力例(4行を1行に変換)
name1  name2  name3    name4
-----  -----  -------  ------
Al     Ben    Charlie  David
Ed     Frank  Greg     Howard
Ida    Joe    Ken      Larry
Mike   null   null     null

出力例(5行を1行に変換)
name1  name2  name3    name4  name5
-----  -----  -------  -----  -----
Al     Ben    Charlie  David  Ed
Frank  Greg   Howard   Ida    Joe
Ken    Larry  Mike     null   null

SQLパズル 第2版のパズル62 [レポートの整形] を参考にさせていただきました

データ作成スクリプト

create table Names(
name char(10),
primary key (name));

insert into Names values('Al');
insert into Names values('Ben');
insert into Names values('Charlie');
insert into Names values('David');
insert into Names values('Ed');
insert into Names values('Frank');
insert into Names values('Greg');
insert into Names values('Howard');
insert into Names values('Ida');
insert into Names values('Joe');
insert into Names values('Ken');
insert into Names values('Larry');
insert into Names values('Mike');
commit;

SQL

--■■■Lead関数とRow_Number関数を使用する方法■■■
select name1,name2,name3,name4,name5
from (select Row_Number() over(order by name) as Rank,
      name as name1,
      Lead(name,1) over(order by name) as name2,
      Lead(name,2) over(order by name) as name3,
      Lead(name,3) over(order by name) as name4,
      Lead(name,4) over(order by name) as name5
      from Names)
where mod(Rank,5)=1
order by name1;

--■■■withとRow_Number関数と外部結合を使用する方法■■■
with WorkView as (select Row_Number() over(order by name) as Rank,name from Names)
select a.name as name1,b.name as name2,c.name as name3,d.name as name4,e.name as name5
from WorkView a,WorkView b,WorkView c,WorkView d,WorkView e
where mod(a.Rank,5)=1
  and a.Rank+1=b.Rank(+)
  and a.Rank+2=c.Rank(+)
  and a.Rank+3=d.Rank(+)
  and a.Rank+4=e.Rank(+)
order by a.name;

--■■■withとRow_Number関数とスカラー問い合わせを使用する方法■■■
with WorkView as (select Row_Number() over(order by name) as Rank,name from Names)
select a.name as name1,
(select b.name from WorkView b where b.Rank=a.Rank+1) as name2,
(select b.name from WorkView b where b.Rank=a.Rank+2) as name3,
(select b.name from WorkView b where b.Rank=a.Rank+3) as name4,
(select b.name from WorkView b where b.Rank=a.Rank+4) as name5
from WorkView a
where mod(Rank,5)=1
order by name;

--■■■相関サブクエリを使用する方法■■■
select a.name as name1,
(select b.name from Names b
  where (select count(c.name)+1 from Names c where c.name < b.name) =
        (select count(c.name)+1 from Names c where c.name < a.name)+1) as name2,
(select b.name from Names b
  where (select count(c.name)+1 from Names c where c.name < b.name) =
        (select count(c.name)+1 from Names c where c.name < a.name)+2) as name3,
(select b.name from Names b
  where (select count(c.name)+1 from Names c where c.name < b.name) =
        (select count(c.name)+1 from Names c where c.name < a.name)+3) as name4,
(select b.name from Names b
  where (select count(c.name)+1 from Names c where c.name < b.name) =
        (select count(c.name)+1 from Names c where c.name < a.name)+4) as name5
from Names a
where mod((select count(b.name)+1 from Names b where b.name < a.name),5) = 1
order by name;

--■■■グループ化する方法■■■
select max(decode(mod(Rank,5),1,name)) as name1,
       max(decode(mod(Rank,5),2,name)) as name2,
       max(decode(mod(Rank,5),3,name)) as name3,
       max(decode(mod(Rank,5),4,name)) as name4,
       max(decode(mod(Rank,5),0,name)) as name5
from (select name,Row_Number() over(order by name) as Rank
        from Names)
group by ceil(Rank/5)
order by ceil(Rank/5);

解説

Lead関数とRow_Number関数を使用する方法では、
Lead関数で、ソートした時の後続の行の列の値を取得してます。

他の方法では、
nameの値を使って、順位を求め、
順位を5で割った剰余を使って、行列変換してます。

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

マニュアル(WITH句を使用した計算)
SQL 問い合わせ - スカラー副問い合わせ、cursor、row_number、over、with - SAK Streets

---