MySQLの、オプション指定のgroup_concat関数を模倣し、
以下と同じ結果を取得する、OracleのSQLを記述する。
mysql> select ID,sum(Val) as SumVal,
-> group_concat(distinct Str order by Str desc) as ConcatStr
-> from (select 1 as ID,100 as Val,'aaa' as Str
-> union all select 1,150,'bbb'
-> union all select 1,200,'aaa'
-> union all select 2,300,'ccc'
-> union all select 2,400,'ddd'
-> union all select 2,450,'ddd'
-> union all select 2,450,'ddd'
-> union all select 2,550,'eee') dummy
-> group by ID;
+----+--------+-------------+
| ID | SumVal | ConcatStr |
+----+--------+-------------+
| 1 | 450 | bbb,aaa |
| 2 | 2150 | eee,ddd,ccc |
+----+--------+-------------+
col ConcatStr for a20
--■■■階層問い合わせを使う方法■■■
with WorkView as (select 1 as ID,100 as Val,'aaa' as Str from dual
union all select 1,150,'bbb' from dual
union all select 1,200,'aaa' from dual
union all select 2,300,'ccc' from dual
union all select 2,400,'ddd' from dual
union all select 2,450,'ddd' from dual
union all select 2,450,'ddd' from dual
union all select 2,550,'eee' from dual)
select ID,SumVal,SubStr(sys_connect_by_path(Str,','),2) as ConcatStr
from (select ID,Str,
sum(Val) over(partition by ID) as SumVal,
count(distinct Str) over(partition by ID) as MaxLevel,
Dense_Rank() over(partition by ID order by Str desc) as Rank1,
Row_Number() over(partition by ID,Str order by 1) as Rank2
from WorkView)
where Level = MaxLevel
Start With 1 = all(Rank1,Rank2)
connect by prior ID = ID
and prior Rank1 = Rank1 - 1
and Rank2 = 1;
--■■■model句を使う方法(10g以降)■■■
with WorkView as (select 1 as ID,100 as Val,'aaa' as Str from dual
union all select 1,150,'bbb' from dual
union all select 1,200,'aaa' from dual
union all select 2,300,'ccc' from dual
union all select 2,400,'ddd' from dual
union all select 2,450,'ddd' from dual
union all select 2,450,'ddd' from dual
union all select 2,550,'eee' from dual)
select ID,SumVal,max(ConcatStr) as ConcatStr
from (select ID,SumVal,ConcatStr
from WorkView
model
partition by (ID)
dimension by (Row_Number() over(partition by ID order by Val) as rn)
measures(Val,9999 as SumVal,cast(Str as varchar2(200)) as ConcatStr)
rules(
ConcatStr[any] order by rn=
case when cv(rn) = 1 then ConcatStr[cv()]
when instr(',' || ConcatStr[cv()-1] || ',',',' || ConcatStr[cv()] || ',') > 0
then ConcatStr[cv()-1]
else ConcatStr[cv()-1] || ',' || ConcatStr[cv()] end,
SumVal[any] = sum(Val)[any]))
group by ID,SumVal;