Re: smashed by a problem: query to groupped hierarchical data

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Fri, 27 Jul 2007 19:00:16 +0200
Message-ID: <46aa249e$0$9378$426a74cc@news.free.fr>

"T-BAG" <T-BAG_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,
| T-BAG
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 Received on Fri Jul 27 2007 - 12:00:16 CDT