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
"TBAG" <TBAG_at_prisonbreak.invalid.com> a écrit dans le message de news: 46a8ff06$0$26249$c3e8da3_at_news.astraweb.com...
 Hello!

 I am smashed by the following problem. There is a table containing
 elements in hierarchical relation. Here is an example:

 nbr child A/R parent group
 
 1  P1  A  
 2  P2  A  
 3  P3  A  
 4  T1  R  P1  T
 5  T5  A  P1  T
 6  T4  A  P3  T
 7  A1  N  P2  H
 8  A7  A  P3  H

 For instance, at line number 8 there is an element A7, which belongs to
 group H, was added (A), and its parent element is P3.

 When I choose P1 element I would like to obtain the following row of data:

  grup without  group T  group H
  a name  parent=P1  !=P1  parent=P1  !=P1
 
 P1  A(P1),A(P2),A(P3)  R(T1),A(T5)  A(T4)  N(A1),A(A7)

 I cannot think up any query to get the required result. I tried to use
 rank() type functions, and CONNECT_BY_PATH examples, but with no luck.
 Could anybody help with the efficient solution?

 I would greatly appreciate your suggestions.

 Thank you,
 TBAG
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
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')), grp19 )
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))) HnP125 from aggreg
group T group T group H group H without group with parent P1 with parent !P1with 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
Received on Fri Jul 27 2007  12:00:16 CDT