7-52 完全外部結合で行列変換

SQLパズル

人テーブル
    名前  性別
--------  ----
木曽義仲    男
源義経      男
源頼朝      男
静御前      女
北条政子    女

人テーブルを、以下のように行列変換して出力する。
(男と女の組み合わせは、任意)

出力結果
      男        女  Rank
--------  --------  ----
源義経    静御前       1
源頼朝    北条政子     2
木曽義仲      null     3

データ作成スクリプト

create table 人(
名前 varchar2(8),
性別 char(2));

insert into 人 values('木曽義仲','男');
insert into 人 values('源義経'  ,'男');
insert into 人 values('源頼朝'  ,'男');
insert into 人 values('静御前'  ,'女');
insert into 人 values('北条政子','女');
commit;

SQL

--■■■名前でソートする方法■■■
with Work男 as (
select 名前,性別,Row_Number() over(order by 名前) as Rank
  from 人
 where 性別 = '男'),
Work女 as (
select 名前,性別,Row_Number() over(order by 名前) as Rank
  from 人
 where 性別 = '女')
select a.名前 as 男,b.名前 as 女,
coalesce(a.Rank,b.Rank) as Rank
from Work男 a full join Work女 b
on (a.Rank=b.Rank)
order by coalesce(a.Rank,b.Rank);

--■■■ランダムにソートする方法■■■
with Work男 as (
select 名前,性別,Row_Number() over(order by dbms_random.random()) as Rank
  from 人
 where 性別 = '男'),
Work女 as (
select 名前,性別,Row_Number() over(order by dbms_random.random()) as Rank
  from 人
 where 性別 = '女')
select a.名前 as 男,b.名前 as 女,
coalesce(a.Rank,b.Rank) as Rank
from Work男 a full join Work女 b
on (a.Rank=b.Rank)
order by coalesce(a.Rank,b.Rank);

--■■■model句を使う方法(10g以降)■■■
select 男,女,PID
  from 人
 model RETURN UPDATED ROWS
 partition by (Row_number() over(partition by 性別 order by 名前) as PID)
 DIMENSION BY (性別)
 measures(名前 as 男,名前 as 女)
 rules(
 男['Wk'] = 男['男'],
 女['Wk'] = 女['女']);

解説

Row_Number関数で、1からの連番を割り、完全外部結合してます。

---