create table workT(ID,Val) as
select 1,'AAA' from dual union all
select 1,'BBB' from dual union all
select 1,'CCC' from dual union all
select 1,'CCC' from dual union all
select 1,'DDD' from dual union all
select 2,'EEE' from dual union all
select 2,'EEE' from dual union all
select 2,'EEE' from dual union all
select 2,'EEE' from dual union all
select 2,'FFF' from dual union all
select 2,'FFF' from dual union all
select 2,'GGG' from dual;
There is one of usage of "wm_sys.wm_concat" and emulate it.
col conStr for a50
select ID,Val,
wm_sys.wm_concat(Val)
over(partition by ID order by Val) as conStr
from workT
select ID,aVal,
substr(sys_connect_by_path(bVal,','),2) as conStr
from (select a.ID,a.Val as aVal,b.Val as bVal,
a.RowID as Row_ID,
Row_Number()
over(partition by a.RowID order by b.Val) as rn
from workT a,workT b
where a.ID = b.ID
and a.Val >= b.Val)
where connect_by_IsLeaf = 1
Start With rn=1
connect by prior rn+1 = rn
and prior Row_ID = Row_ID
order by ID,aVal;
ID aVal conStr
-- ---- ---------------------------
1 AAA AAA
1 BBB AAA,BBB
1 CCC AAA,BBB,CCC,CCC
1 CCC AAA,BBB,CCC,CCC
1 DDD AAA,BBB,CCC,CCC,DDD
2 EEE EEE,EEE,EEE,EEE
2 EEE EEE,EEE,EEE,EEE
2 EEE EEE,EEE,EEE,EEE
2 EEE EEE,EEE,EEE,EEE
2 FFF EEE,EEE,EEE,EEE,FFF,FFF
2 FFF EEE,EEE,EEE,EEE,FFF,FFF
2 GGG EEE,EEE,EEE,EEE,FFF,FFF,GGG
However,I want to know "model solution" which is tablseScan once only.
Can we make it?
I supoose we must use TableFunction.
I am using Oracle10gR2.