10-270 1列ごとのdistinct

SQLパズル

tab1
animal  house  fred
------  -----  ----
dog        10     2
dog        20     1
dog        40     2
cat        30     4
cat        10     3
rabbit     10     2
rabbit     50     1

1列ごとにdistinctする。
各列の並びは昇順とする。

出力結果
animal  house  fred
------  -----  ----
cat        10     1
dog        20     2
rabbit     30     3
null       40     4
null       50  null

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table tab1(animal,house,fred) as
select 'dog'   ,10,2 from dual union all
select 'dog'   ,20,1 from dual union all
select 'dog'   ,40,2 from dual union all
select 'cat'   ,30,4 from dual union all
select 'cat'   ,10,3 from dual union all
select 'rabbit',10,2 from dual union all
select 'rabbit',50,1 from dual;

SQL

--■■■union allを3回使う方法■■■
select max(decode(ID,1,animal)) as animal,
       max(decode(ID,2,house))  as house,
       max(decode(ID,3,fred))   as fred
from (select distinct 1 as ID,dense_rank() over(order by animal) as rn,
      animal,to_number(null) as house,to_number(null) as fred from tab1 union all
      select distinct 2      ,dense_rank() over(order by house),
      to_char(null),house,to_number(null) from tab1 union all
      select distinct 3      ,dense_rank() over(order by fred),
      to_char(null),to_number(null),fred from tab1)
group by rn
order by rn;

--■■■model句を使う方法(10g以降)■■■
select animal,house,fred
from (select animal,house,fred
        from tab1
       model
       dimension by (RowNum as soeji,
                     dense_rank() over(order by animal) as Rn1,
                     dense_rank() over(order by house)  as Rn2,
                     dense_rank() over(order by fred)   as Rn3)
       measures(animal,house,fred,
                animal a,house h,fred f)
       rules(
       animal[any,any,any,any] = max(a)[any,cv(soeji),any,any],
       house[any,any,any,any]  = max(h)[any,any,cv(soeji),any],
       fred[any,any,any,any]   = max(f)[any,any,any,cv(soeji)]))
 where animal is not null
    or nvl(house,fred) is not null
order by animal,house,fred;

解説

union allを3回使う方法が最もシンプルでしょう。

---