model句04 wmsys.wm_concatの代用

SQLパズル

strTable
SortKey  Val
-------  ---
     10  AAA
     20  BBB
     30  CCC
     40  DDD
     50  EEE
     60  FFF
     70  GGG
     80  HHH
     90  III

wmsys.wm_concatを使った以下と同じ結果を
マニュアルに載っている機能を使ったクエリで取得する。

select SortKey,wmsys.wm_concat(Val) over(order by SortKey) as ConcatVal
  from (select SortKey,
        case when Row_Number() over(order by SortKey) <= 4
             then Val end as Val
          from strTable)
order by SortKey;

出力結果
SortKey  ConcatVal
-------  ---------------
     10  AAA
     20  AAA,BBB
     30  AAA,BBB,CCC
     40  AAA,BBB,CCC,DDD
     50  AAA,BBB,CCC,DDD
     60  AAA,BBB,CCC,DDD
     70  AAA,BBB,CCC,DDD
     80  AAA,BBB,CCC,DDD
     90  AAA,BBB,CCC,DDD

データ作成スクリプト

create table strTable(SortKey,Val) as
select 10,'AAA' from dual union
select 20,'BBB' from dual union
select 30,'CCC' from dual union
select 40,'DDD' from dual union
select 50,'EEE' from dual union
select 60,'FFF' from dual union
select 70,'GGG' from dual union
select 80,'HHH' from dual union
select 90,'III' from dual;

SQL

select SortKey,LTrim(RTrim(ConcatVal,','),',') as ConcatVal
  from (select SortKey,Row_Number() over(order by SortKey) as Rn,
        case when Row_Number() over(order by SortKey) <= 4
             then cast(Val as varchar2(40)) end as Val
          from strTable)
 model
 dimension by (Rn)
 measures(SortKey,Val,Val ConcatVal)
 rules(ConcatVal[Rn is any] order by Rn
     = ConcatVal[CV(RN)-1] || ',' || Val[CV(Rn)]);

解説

OracleでMySQLのgroup_concatを模倣する1つの方法としてmodel句を使うものです。
wmsys.wm_concatは、Oracle11gのマニュアルにすらのってないので、model句を使うのは十分有力でしょう。

10-218 wmsys.wm_concat
10-262 累計を求めるwm_concatをXML関数で模倣

model句22 MySQLのgroup_concatもどき

---