Forum Stats

  • 3,770,131 Users
  • 2,253,073 Discussions
  • 7,875,337 Comments

Discussions

SQL - Hierarchical View

433024
433024 Member Posts: 120
edited Jan 23, 2007 2:10AM in SQL & PL/SQL
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

Comments

  • 507047
    507047 Member Posts: 162
    Can you post data for this? Seems the query is incomplete.
    Post few more details.

    Cheers
    Ram
  • 438877
    438877 Member Posts: 1,849
    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.
  • ushitaki
    ushitaki Member Posts: 1,128
    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
  • 438877
    438877 Member Posts: 1,849
    edited Jan 22, 2007 8:22AM
    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.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Jan 23, 2007 2:10AM
    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
    1575292
This discussion has been closed.