10-315 最大値の行の各値を1行にまとめる

SQLパズル

PivTest
Name      Val  DT
--------  ---  ----------
Personal  111  2010-03-11
Personal  999  2010-04-23
Personal  444  2010-05-29
Shared    555  2010-03-11
Shared    333  2010-04-25

NameがPersonalでDTが最大の行の各値と、
NameがSharedでDTが最大の行の各値を1行にまとめる。

出力結果
P_VAL  P_NAME    P_DT        S_VAL  S_NAME  S_DT
-----  --------  ----------  -----  ------  ----------
  444  Personal  2010-05-29    333  Shared  2010-04-25

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

データ作成スクリプト

create table PivTest(Name,Val,DT) as
select 'Personal',111,date '2010-03-11' from dual union
select 'Personal',999,date '2010-04-23' from dual union
select 'Personal',444,date '2010-05-29' from dual union
select 'Shared'  ,555,date '2010-03-11' from dual union
select 'Shared'  ,333,date '2010-04-25' from dual;

SQL

--■■■max関数とdecode関数を組み合わせる方法■■■
select
max(Val)  Keep(Dense_Rank Last order by decode(Name,'Personal',DT) nulls first) as Val,
max(decode(Name,'Personal',Name) as NAME,
max(decode(Name,'Personal',DT)) as DT,
max(Val)  Keep(Dense_Rank Last order by decode(Name,'Shared',DT) nulls first) as SHARED_Val,
max(decode(Name,'Shared',Name) as SHARED_Name,
max(decode(Name,'Shared',DT)) as SHARED_DT
  from PivTest;

--■■■Pivotを使う方法(11g以降)■■■
select *
  from PivTest
pivot (max(Val)  Keep(Dense_Rank Last order by DT) as Val,
       max(Name) as Name,
       max(dt) as dt
       for Name in ('Personal' as P,'Shared' as S));

解説

この場合は、Pivotを使ったほうがシンプルだと思いますねぇ

max関数とdecode関数を組み合わせる方法で、
Keep指定して、nulls firstを使うのは、シンプルとは思えないので

10-278 unpivotとpivot

---