Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

WITH PERSONS(ID, PNAME) AS

(

SELECT 1, 'P1' FROM DUAL UNION ALL

SELECT 2, 'P2' FROM DUAL

Branches(ID, Title, BrachIDRef) AS

(

SELECT 0 ,'Master', null from dual union all

SELECT 1 ,'B1', 0 from dual union all

SELECT 2 ,'B2', 1 from dual union all

SELECT 3 ,'B3', 2 from dual union all

SELECT 4 ,'B4', 2 from dual union all

SELECT 5 ,'B5', 1 from dual union all

SELECT 6 ,'B6', 0 from dual union all

SELECT 7 ,'B7', 6 from dual

PersonBranches(Id,PersonIDref,BranchIDref)

(

SELECT 1,1,1 FROM DUAL UNION ALL

SELECT 2,2,6 FROM DUAL UNION ALL

SELECT 3,2,2 FROM DUAL

cte as

(SELECT ANCESTOR, LISTAGG(case when Title = ANCESTOR then null else title end, '/') within group (order by null) as pth from

(SELECT DISTINCT ID,

CONNECT_BY_ROOT Title as ANCESTOR,

Title

FROM Branches

CONNECT BY PRIOR ID = BrachIDRef)

GROUP BY

ANCESTOR)

SELECT pb.id, p.pname, b.title

from PersonBranches pb