This discussion is archived
5 Replies Latest reply: Jan 22, 2007 11:10 PM by Aketi Jyuuzou RSS

SQL - Hierarchical View

433024 Newbie
Currently Being Moderated
Hi,

SELECT      lvl, TRIM(decode(lvl, 1, coa_name)) "Level 1",
     TRIM(decode(lvl, 2, coa_name)) "Level 2",
     TRIM(decode(lvl, 3, coa_name)) "Level 3",
     TRIM(decode(lvl, 4, coa_name)) "Level 4"
FROM     coa_tree
order by coa_g_code

the above statement return me data as follows:
Level
1 2 3 4
A - - -
- B
- - C
- - - D
- - - E
- - F
- - - G
- - H
- - - I
- - J
K
- L
- - M
- - - N
- - - O
P
Q
but i need output as bellow:
Level
1 2 3 4
A B C D
- - - E
- - F G
- - H I     
- - J
K L M N
- - - O
- - P Q

will it possible?

(i enabled to make this output format correctly due to editor problem. kindly consider "-" as null value)

thanks
RD
  • 1. Re: SQL - Hierarchical View
    507047 Newbie
    Currently Being Moderated
    Can you post data for this? Seems the query is incomplete.
    Post few more details.

    Cheers
    Ram
  • 2. Re: SQL - Hierarchical View
    438877 Explorer
    Currently Being Moderated
    Would be better you provided sample data and view code.
    Trying to simulate your case:
    SQL> select * from vt;

    C         LV         RN
    - ---------- ----------
    A          1          1
    B          2          2
    C          3          3
    D          4          4
    E          4          5
    F          3          6
    G          4          7
    H          3          8
    I          4          9
    J          3         10
    K          1         11
    L          2         12
    M          3         13
    N          4         14
    O          4         15

    15 rows selected.

    SQL> SELECT lv, TRIM(decode(lv, 1, c)) "Level 1",
      2  TRIM(decode(lv, 2, c)) "Level 2",
      3  TRIM(decode(lv, 3, c)) "Level 3",
      4  TRIM(decode(lv, 4, c)) "Level 4"
      5  FROM vt
      6  order by rn;

            LV L L L L
    ---------- - - - -
             1 A
             2   B
             3     C
             4       D
             4       E
             3     F
             4       G
             3     H
             4       I
             3     J
             1 K
             2   L
             3     M
             4       N
             4       O

    15 rows selected.

    SQL> select l1, l2, l3, l4 from (
      2  select
      3  case when lg=1 and lv =1 then c end l1,
      4  case when lg=1 and lv =1 then lead(c,1) over(order by rn)
      5  when lg = 1 and lv != 1 then null else decode(lv, 2, c) end l2,
      6  case when lg=1 and lv =1 then lead(c,2) over(order by rn)
      7  when lg = 1 and lv != 1 then null else decode(lv, 3, c) end l3,
      8  case when lg=1 and lv =1 then lead(c,3) over(order by rn)
      9  when lg = 1 and lv != 1 then null else decode(lv, 4, c) end l4,
    10  rn
    11  from
    12  (
    13  SELECT lv, c, rn,
    14  min(lv) over(order by rn rows between 3 preceding and current row) lg
    15  FROM vt
    16  )
    17  ) where l1||l2||l3||l4 is not null
    18  order by rn
    19  /

    L L L L
    - - - -
    A B C D
          E
        F
          G
        H
          I
        J
    K L M N
          O

    9 rows selected.
    So maybe (not tested !!):

    select l1, l2, l3, l4 from (
    select
    case when lg=1 and lv =1 then coa_name end l1,
    case when lg=1 and lv =1 then lead(coa_name,1) over(order by coa_g_code)
    when lg = 1 and lv != 1 then null else decode(lv, 2, coa_name) end l2,
    case when lg=1 and lv =1 then lead(coa_name,2) over(order by coa_g_code)
    when lg = 1 and lv != 1 then null else decode(lv, 3, coa_name) end l3,
    case when lg=1 and lv =1 then lead(coa_name,3) over(order by coa_g_code)
    when lg = 1 and lv != 1 then null else decode(lv, 4, coa_name) end l4,
    rn
    from
    (
    SELECT lvl, coa_name, coa_g_code,
    min(lvl) over(order by coa_g_code rows between 3 preceding and current row) lg
    FROM coa_tree
    )
    ) where l1||l2||l3||l4 is not null
    order by coa_g_code

    Rgds.
  • 3. Re: SQL - Hierarchical View
    ushitaki Newbie
    Currently Being Moderated
    create table coa_tree(
    coa_g_code numeric(3)
    ,lvl numeric(1)
    ,coa_name varchar(2)
    )
    ;

    insert into coa_tree
    select 1,1,'A' from dual union all
    select 2,2,'B' from dual union all
    select 3,3,'C' from dual union all
    select 4,4,'D' from dual union all
    select 5,4,'E' from dual union all
    select 6,3,'F' from dual union all
    select 7,4,'G' from dual union all
    select 8,3,'H' from dual union all
    select 9,4,'I' from dual union all
    select 10,3,'J' from dual union all
    select 11,1,'K' from dual union all
    select 12,2,'L' from dual union all
    select 13,3,'M' from dual union all
    select 14,4,'N' from dual union all
    select 15,4,'O' from dual union all
    select 16,3,'P' from dual union all
    select 17,4,'Q' from dual
    ;

    commit;

    set null "-"

    select nullif(lvl_1,
    lag(lvl_1) over (order by coa_g_code))
    as "1"
    ,nullif(lvl_2,
    lag(lvl_2) over (order by coa_g_code))
    as "2"
    ,nullif(lvl_3,
    lag(lvl_3) over (order by coa_g_code))
    as "3"
    ,nullif(lvl_4,
    lag(lvl_4) over (order by coa_g_code))
    as "4"
    from (
    select coa_g_code
    ,lvl
    ,lead(lvl,1)
    over (order by coa_g_code)
    as next_lvl
    ,last_value(decode(lvl,1,coa_name) ignore nulls)
    over (order by coa_g_code)
    as lvl_1
    ,last_value(decode(lvl,2,coa_name) ignore nulls)
    over (order by coa_g_code)
    as lvl_2
    ,last_value(decode(lvl,3,coa_name) ignore nulls)
    over (order by coa_g_code)
    as lvl_3
    ,last_value(decode(lvl,4,coa_name) ignore nulls)
    over (order by coa_g_code)
    as lvl_4
    from coa_tree
    )
    where next_lvl is null
    or next_lvl <= lvl
    order by coa_g_code
    ;

    -- drop table coa_tree;

    ## Results

    1 2 3 4
    -- -- -- --
    A B C D
    - - - E
    - - F G
    - - H I
    - - J -
    K L M N
    - - - O
    - - P Q
  • 4. Re: SQL - Hierarchical View
    438877 Explorer
    Currently Being Moderated
    Thanks ushitaki for the clarification of possible logic. Then it can be too:
    SQL> select * from coa_tree;

    COA_G_CODE        LVL CO
    ---------- ---------- --
             1          1 A
             2          2 B
             3          3 C
             4          4 D
             5          4 E
             6          3 F
             7          4 G
             8          3 H
             9          4 I
            10          3 J
            11          1 K
            12          2 L
            13          3 M
            14          4 N
            15          4 O
            16          3 P
            17          4 Q

    17 rows selected.

    SQL> select
      2  max(decode(lvl,1,coa_name)) "Level1",
      3  max(decode(lvl,2,coa_name)) "Level2",
      4  max(decode(lvl,3,coa_name)) "Level3",
      5  max(decode(lvl,4,coa_name)) "Level4"
      6  from (
      7  select coa_g_code-lvl gr, lvl, coa_name from coa_tree
      8  ) group by gr
      9  order by gr
    10  /

    Le Le Le Le
    -- -- -- --
    A  B  C  D
             E
          F  G
          H  I
          J
    K  L  M  N
             O
          P  Q

    8 rows selected.
    or in more general case
    SQL> select * from coa_tree;

    COA_G_CODE        LVL CO
    ---------- ---------- --
            10          1 A
            20          2 B
            30          3 C
            40          4 D
            50          4 E
            60          3 F
            70          4 G
            80          3 H
            90          4 I
           100          3 J
           110          1 K
           120          2 L
           130          3 M
           140          4 N
           150          4 O
           160          3 P
           170          4 Q

    17 rows selected.

    SQL> select
      2  max(decode(lvl,1,coa_name)) "Level1",
      3  max(decode(lvl,2,coa_name)) "Level2",
      4  max(decode(lvl,3,coa_name)) "Level3",
      5  max(decode(lvl,4,coa_name)) "Level4"
      6  from (
      7  select lvl, coa_name, row_number() over(order by coa_g_code)-lvl gr from coa_tree
      8  ) group by gr
      9  order by gr
    10  /

    Le Le Le Le
    -- -- -- --
    A  B  C  D
             E
          F  G
          H  I
          J
    K  L  M  N
             O
          P  Q

    8 rows selected.
    Rgds.
  • 5. Re: SQL - Hierarchical View
    Aketi Jyuuzou Oracle ACE
    Currently Being Moderated
    with coa_tree as(
    select  10 as coa_g_code,1 as lvl,'A' as coa_name from dual union all
    select  21,2,'B' from dual union all
    select  32,3,'C' from dual union all
    select  40,4,'D' from dual union all
    select  55,4,'E' from dual union all
    select  60,3,'F' from dual union all
    select  75,4,'G' from dual union all
    select  80,3,'H' from dual union all
    select  90,4,'I' from dual union all
    select 101,3,'J' from dual union all
    select 110,1,'K' from dual union all
    select 122,2,'L' from dual union all
    select 130,3,'M' from dual union all
    select 144,4,'N' from dual union all
    select 155,4,'O' from dual union all
    select 166,3,'P' from dual union all
    select 170,4,'Q' from dual union all
    select 500,1,'R' from dual union all -- add !!!
    select 600,3,'S' from dual)          -- add !!!
    select rtrim(
    '/' || max(decode(lvl,1,coa_name,'-'))
    || '/' ||max(decode(lvl,2,coa_name,'-'))
    || '/' ||max(decode(lvl,3,coa_name,'-'))
    || '/' ||max(decode(lvl,4,coa_name)),'/-')
    from (select coa_g_code,lvl,coa_name,sum(willSum) over(order by coa_g_code) as gcode
            from (select coa_g_code,lvl,coa_name,
                  case when Lag(lvl) over(order by coa_g_code) < lvl then 0 else 1 end as willSum
                    from coa_tree))
    group by gcode
    order by gcode;
    RTRIM('/
    --------
    /A/B/C/D
    /-/-/-/E
    /-/-/F/G
    /-/-/H/I
    /-/-/J
    /K/L/M/N
    /-/-/-/O
    /-/-/P/Q
    /R/-/S
    Elic's great solution
    Re: Group by preserving the order