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
T-BAG wrote:
> 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
Post your code and name your version.
My first thought is that the data needs to be normalized but it is late and I don't have the time right now to give it the attention it needs.
-- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Fri Jul 27 2007 - 02:00:35 CDT