Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: smashed by a problem: query to groupped hierarchical data
> SQL> with
> 2 data as (
> 3 select connect_by_root child root, grp,
> 4 decode(grp,
> 5 null,ar||'('||child||')',
> 6 substr(sys_connect_by_path(ar||'('||child||')',','),
> 7 instr(sys_connect_by_path(ar||'('||child||')',','),
> 8 ',',2)+1))
> 9 elem
> 10 from t
> 11 connect by prior child = parent and nvl(prior grp,grp) = grp
> 12 start with parent is null
> 13 ),
> 14 aggreg as (
> 15 select decode(grp,null,null,decode(root,'P1','P1','!P1')) root, grp,
> 16 stragg(elem) value
> 17 from data
> 18 group by decode(grp,null,null,decode(root,'P1','P1','!P1')), grp
> 19 )
> 20 select max(decode(grp,null,value)) wg,
> 21 max(decode(grp,'T',decode(root,'P1',value))) TP1,
> 22 max(decode(grp,'T',decode(root,'P1',null,value))) TnP1,
> 23 max(decode(grp,'H',decode(root,'P1',value))) HP1,
> 24 max(decode(grp,'H',decode(root,'P1',null,value))) HnP1
> 25 from aggreg
> 26 /
>
> |group T |group T |group H |group H
> without group |with parent P1 |with parent !P1|with parent P1 |with parent !P1
> -----------------|---------------|---------------|---------------|---------------
> A(P1),A(P2),A(P3)|R(T1),A(T5) |A(T4) | |N(A1),A(A7)
>
> 1 row selected.
>
> Regards
> Michel Cadot
Hello,
Michel, thanks for the code. It is impressive to me. I tried to find out what the stragg function is, and found Tom Kyte's solutions of the same name. I used the code found here:
http://www.dbazine.com/oracle/or-articles/tropashko2
but I get different result than yours, ie. the "without group" colum is 'a(p1)a(p2)a(p3)' but others are null.
Could you post your definition of stragg function?
Thank you,
T-BAG
Received on Fri Jul 27 2007 - 18:57:57 CDT
![]() |
![]() |