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

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

col ConcatVal for a20

select a.SortKey,
Rtrim(substr(
replace(
replace(XMLAgg(XMLElement("dummy",b.Val) order by b.SortKey)
       ,'</dummy>')
       ,'<dummy>',',')
,2),',') as ConcatVal
  from strTable a,
       (select SortKey,
        case when Row_Number() over(order by SortKey) <= 4
             then Val end as Val
          from strTable) b
 where a.SortKey >= b.SortKey
group by a.SortKey
order by a.SortKey;

解説

内部結合を使ってます。

10-218 wmsys.wm_concat
model句04 wmsys.wm_concatの代用
model句22 MySQLのgroup_concatもどき

---