smashed by a problem: query to groupped hierarchical data

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 Received on Thu Jul 26 2007 - 15:09:42 CDT