Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

emulate "wm_sys.wm_conat over(order by sKey)" using "model caluse"

Aketi JyuuzouApr 14 2009 — edited Nov 4 2010
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.
This post has been answered by Rob van Wijk on Apr 14 2009
Jump to Answer

Comments

Rob van Wijk
http://rwijk.blogspot.com/2008/05/string-aggregation-with-model-clause.html
1 person found this helpful
Aketi Jyuuzou
Thanks reply.
But my sample has duplicate rows.
Then it is not easy that we can use Row_number
I guess we have to use iteration_number and other model feature

In other words.
BelowResult is not correct. 
col conStr for a50

select *
  from workT model
partition by (ID)
dimension by(Row_Number() over(partition by ID order by Val) as rn)
measures(Val,cast(null as varchar2(50)) as conStr)
rules(
conStr[any] order by rn = conStr[cv()-1] || ',' || Val[cv()]);

ID  RN  VAL  conStr
--  --  ---  ----------------------------
 1   1  AAA  ,AAA
 1   2  BBB  ,AAA,BBB
 1   3  CCC  ,AAA,BBB,CCC          <-correct result is AAA,BBB,CCC,CCC
 1   4  CCC  ,AAA,BBB,CCC,CCC
 1   5  DDD  ,AAA,BBB,CCC,CCC,DDD
 2   1  EEE  ,EEE                  <-correct result is EEE,EEE,EEE,EEE
 2   2  EEE  ,EEE,EEE              <-correct result is EEE,EEE,EEE,EEE
 2   3  EEE  ,EEE,EEE,EEE          <-correct result is EEE,EEE,EEE,EEE
 2   4  EEE  ,EEE,EEE,EEE,EEE
 2   5  FFF  ,EEE,EEE,EEE,EEE,FFF
 2   6  FFF  ,EEE,EEE,EEE,EEE,FFF,FFF
 2   7  GGG  ,EEE,EEE,EEE,EEE,FFF,FFF,GGG
561825
I have enhanced slightly the solution of Rob.
select id, val, constr
  from workT model
partition by (ID)
dimension by(Row_Number() over(partition by ID order by Val) as rn)
measures(Val,cast(null as varchar2(50)) as conStr, count(*) over(partition by id, val) as cnt)
rules(
conStr[any] order by rn = trim(both ',' from (conStr[cv()-1]) || ',' || 
                          case when val[cv()] != val[cv() - 1] or constr[CV() - 1] is null then
                                                      rpad(val[cv()] || ',', cnt[cv()] * length(val[cv()]) + cnt[cv()],
                                                           val[cv()] || ','
                                                          )
                                                 End
                              )
)
Regards

Raj
1 person found this helpful
Rob van Wijk
Answer
Hi Aketi,

Ok, that is slightly different indeed. But it can be easily solved by using a max analytic function, like this:
SQL> create table workT(ID,Val) as
  2  select 1,'AAA' from dual union all
  3  select 1,'BBB' from dual union all
  4  select 1,'CCC' from dual union all
  5  select 1,'CCC' from dual union all
  6  select 1,'DDD' from dual union all
  7  select 2,'EEE' from dual union all
  8  select 2,'EEE' from dual union all
  9  select 2,'EEE' from dual union all
 10  select 2,'EEE' from dual union all
 11  select 2,'FFF' from dual union all
 12  select 2,'FFF' from dual union all
 13  select 2,'GGG' from dual
 14  /

Tabel is aangemaakt.

SQL> select id
  2       , rn
  3       , substr(max(constr) over (partition by id,val),2) constr
  4    from ( select *
  5             from workt
  6            model
  7                  partition by (ID)
  8                  dimension by(Row_Number() over(partition by ID order by Val) as rn)
  9                  measures(Val,cast(null as varchar2(50)) as conStr)
 10                  rules
 11                  ( conStr[any] order by rn = conStr[cv()-1] || ',' || Val[cv()]
 12                  )
 13         )
 14  /

        ID         RN CONSTR
---------- ---------- --------------------------------------------------
         1          1 AAA
         1          2 AAA,BBB
         1          3 AAA,BBB,CCC,CCC
         1          4 AAA,BBB,CCC,CCC
         1          5 AAA,BBB,CCC,CCC,DDD
         2          1 EEE,EEE,EEE,EEE
         2          4 EEE,EEE,EEE,EEE
         2          3 EEE,EEE,EEE,EEE
         2          2 EEE,EEE,EEE,EEE
         2          5 EEE,EEE,EEE,EEE,FFF,FFF
         2          6 EEE,EEE,EEE,EEE,FFF,FFF
         2          7 EEE,EEE,EEE,EEE,FFF,FFF,GGG

12 rijen zijn geselecteerd.
Regards,
Rob.
Marked as Answer by Aketi Jyuuzou · Sep 27 2020
Aketi Jyuuzou
OOPS.
I did not realize that there are nice RPAD usage and max analytic function usage.
thank you very much.

And I arranged Rob's solution ;-) 
col CONSTR for a50

select ID,VAL,substr(CONSTR,2) as CONSTR
  from workT model
partition by (ID)
dimension by(Row_Number() over(partition by ID order by Val) as rn)
measures(Val,cast(null as varchar2(50)) as conStr)
rules(
conStr[any] order by rn = conStr[cv()-1] || ',' || Val[cv()],
conStr[any] = max(conStr) over(partition by Val));

ID  VAL  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
Aketi Jyuuzou
And I arranged R.Subramanian's splution :-)
select ID,Val,substr(conStr,2) as conStr
  from workT
 model
partition by(ID)
dimension by(Row_Number()
             over(partition by ID order by Val) as rn)
measures(Val,cast(null as varchar2(50)) as conStr,
         sum(Length(Val)+1) over(partition by ID order by Val) as LenB)
rules(
conStr[any] order by rn = RPad(conStr[cv()-1] || ',' || Val[cv()],LenB[cv()]
                              ,',' || Val[cv()]));
http://www.geocities.jp/oraclesqlpuzzle/10-291.html
1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 13 2009
Added on Apr 14 2009
6 comments
2,117 views