drop table y;
create table y(c varchar(1),p varchar2(1));
 
insert into y values('B','A');
insert into y values('C','A');
insert into y values('C','B');
insert into y values('D','C');
commit;
 
select * from y order by c,p;
 
 
 
--желаемый результат:
B => A
 
C => A
C => B
C => B => A
 
D => C
D => C => B
D => C => A
D => C => B =>A
 
 
-- это не то:
select c,listagg(p,' => ') WITHIN GROUP (order by level desc) route
from y
connect by prior c=p
group by c
order by c

SELECT c,SYS_CONNECT_BY_PATH(p, ' => ')path,level
FROM y
CONNECT BY PRIOR p=c
order by 1,level;
 
 
C    PATH               LEVEL
B     => A                  1
B     => B => A         2
B     => C => B => A    3
C     => B                  1
C     => A              1
C     => C => A         2
C     => C => B         2
D     => C                1
 

with y as (
    select 'B' c,'A'p, 1 v from dual union all
    select 'C' c,'A' p, 2 v from dual union all
    select 'C' c,'B' p, 10 v from dual union all
    select 'D' c,'C' p, 4 v from dual --union all
)
SELECT connect_by_root c c,SYS_CONNECT_BY_PATH(p, ' => ')path,level
FROM y
CONNECT BY PRIOR p=c
order by 1,level;

 

  where instr(path,'C=>B')>0