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

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Sat, 28 Jul 2007 08:58:45 +0200
Message-ID: <46aae924$0$18801$426a34cc@news.free.fr>

"T-BAG" <T-BAG_at_prisonbreak.invalid.com> a écrit dans le message de news: 0001d251$0$2920$c3e8da3_at_news.astraweb.com...

|> 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

I don't think I use the latest version.
I use the first one you'll find at
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402

You can do it without stragg function but it is harder to read and slower.

It is not such impressive.
The first part is just a standard hierarchical query, only the way you want to display the data turn it to something harder to read ("A/R(child)" form). The second part just groups the data within the groups you defined (group null, other groups T with parent P1 and with parent non P1...) with the help of a decode (a case should be easier to read). The last part is a standard rows to columns pivot to display on one line.

As it, it works for 2 levels and I think you have to enhance it to get the output you want if you have more than 2 levels (in this case what you should be the result?).

Regards
Michel Received on Sat Jul 28 2007 - 01:58:45 CDT