Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

SQL - Hierarchical View

433024Jan 22 2007 — edited Jan 23 2007
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
Can you post data for this? Seems the query is incomplete.
Post few more details.

Cheers
Ram
438877

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

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

1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 20 2007
Added on Jan 22 2007
5 comments
6,142 views