Analytics Software

Can you post data for this? Seems the query is incomplete.
Post few more details.

Cheers
Ram

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.

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

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.

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