你有分层数据;如果你将路径返回到层次结构的前一级（通过找到路径的前一级），然后你可以使用层次结构查询来重建路径，那么解决这个问题就容易得多：

SELECT name,
       id,
       path,
       SYS_CONNECT_BY_PATH(name, '/') AS name_path
FROM   (
  SELECT name,
         id,
         path, 
         SUBSTR(path, INSTR(path, '/', -1, 2) + 1, INSTR(path, '/', -1, 1) - INSTR(path, '/', -1, 2) - 1) AS prev
  FROM   table_name
)
START WITH prev IS NULL
CONNECT BY PRIOR id = prev;

或：

SELECT name,
       id,
       path,
       SYS_CONNECT_BY_PATH(name, '/') AS name_path
FROM   (
  SELECT name,
         id,
         path,
         SUBSTR(path, 1, INSTR(path, '/', -1, 1) - 1) AS prev
  FROM   table_name
)
START WITH prev IS NULL
CONNECT BY PRIOR path = prev

其中，对于示例数据：

CREATE TABLE table_name ( Name, Id, Path ) AS
SELECT 'Base1', 'B1', '/B1'       FROM DUAL UNION ALL
SELECT 'Mid1',  'M1', '/B2/M1'    FROM DUAL UNION ALL
SELECT 'Top1',  'T1', '/B1/M2/T1' FROM DUAL UNION ALL
SELECT 'Mid2',  'M2', '/B1/M2'    FROM DUAL UNION ALL
SELECT 'Top2',  'T2', '/B2/M1/T2' FROM DUAL UNION ALL
SELECT 'Top3',  'T3', '/B2/M1/T3' FROM DUAL UNION ALL
SELECT 'Base2', 'B2', '/B2'       FROM DUAL;

• 注意：我已经修复了数据，因此路径依次为/B2、/B2/M1/和/B2/M1/T2，而不是在路径中途随机地从B1更改为B2。*

两个输出：
| 姓名|识别号|路径|名称路径|
| - ------|- ------|- ------|- ------|
| 碱基1|地下一层|/B1|/基线1|
| 中期2|M2|/B1/M2|/底座1/中间2|
| 前1名|T1|/B1/M2/T1|/基本1/中间2/顶部1|
| 碱基2|B2|/B2|/基数2|
| 中期1|M1|/B2/M1|/基础2/中间1|
| 前2名|T2|/B2/M1/T2|/基本2/中间1/顶部2|
| 前三名|T3|/B2/M1/T3|/基本2/中间1/前3|
fiddle

备选办法之一可以是：

SELECT
    (Select A_NAME From tbl Where ID = t.E_1) || 
        CASE WHEN t.ELEMENTS > 1 THEN '/' || (Select A_NAME From tbl Where ID = t.E_2) END || 
        CASE WHEN t.ELEMENTS > 2 THEN '/' || (Select A_NAME From tbl Where ID = t.E_3) END || 
        CASE WHEN t.ELEMENTS > 3 THEN '/' || (Select A_NAME From tbl Where ID = t.E_4) END "TXT"
FROM 
    ( Select    t.A_NAME, t.ID, t.A_PATH, 
                Length(t.A_PATH) - Length(Replace(A_PATH, '/', '')) "ELEMENTS",
                SubStr(A_PATH, InStr(t.A_PATH, '/', 1, 1) + 1, 2) "E_1",
                SubStr(A_PATH, CASE WHEN InStr(t.A_PATH, '/', 1, 2) != 0 THEN InStr(t.A_PATH, '/', 1, 2) + 1 END, 2) "E_2",
                SubStr(A_PATH, CASE WHEN InStr(t.A_PATH, '/', 1, 3) != 0 THEN InStr(t.A_PATH, '/', 1, 3) + 1 END, 2) "E_3",
                SubStr(A_PATH, CASE WHEN InStr(t.A_PATH, '/', 1, 4) != 0 THEN InStr(t.A_PATH, '/', 1, 4) + 1 END, 2) "E_4"
      From      tbl t
    ) t

其中包含您的样本数据：

WITH
    tbl(A_NAME, ID, A_PATH) AS 
        (
            Select 'Base1', 'B1', '/B1' From Dual Union All
            Select 'Mid1', 'M1', '/B1/M1' From Dual Union All
            Select 'Top1', 'T1', '/B1/M2/T1' From Dual Union All
            Select 'Mid2', 'M2', '/B1/M2' From Dual Union All
            Select 'Top2', 'T2', '/B2/M1/T2' From Dual Union All
            Select 'Top3', 'T3', '/B2/M1/T3' From Dual Union All
            Select 'Base2', 'B2', '/B2' From Dual 
        )

...结果：

TXT             
-----------------
Base1             
Base1/Mid1        
Base1/Mid2/Top1   
Base1/Mid2        
Base2/Mid1/Top2   
Base2/Mid1/Top3   
Base2

另一个选项是使用MODEL，如下所示：

SELECT    TXT
FROM    (   Select  A_NAME, ID, A_PATH, CAST('xxx' as VarChar2(50)) "TXT"
            From    tbl  )
        MODEL Dimension By (ID)
              Measures(A_NAME, A_PATH, TXT)
              Rules ( 
                      TXT[ANY] = CASE WHEN Length(A_PATH[CV()]) = 3 THEN A_NAME[SubStr(A_PATH[CV()], 2, 2)] 
                                      WHEN Length(A_PATH[CV()]) = 6 THEN A_NAME[SubStr(A_PATH[CV()], 2, 2)] || '/' || A_NAME[SubStr(A_PATH[CV()], 5, 2)] 
                                      WHEN Length(A_PATH[CV()]) = 9 THEN A_NAME[SubStr(A_PATH[CV()], 2, 2)] || '/' || A_NAME[SubStr(A_PATH[CV()], 5, 2)]|| '/' || A_NAME[SubStr(A_PATH[CV()], 8, 2)]
                                  END
                    )

增加（补充方案）

-- Scenario_2:  different origins of data
-- TBL - cte named "tbl" containes ID and A_NAME columns
-- it is irrelevant where are they from as long as you have or could have unique ID's
WITH
    tbl(A_NAME, ID) AS 
        (
          Select 'Base1', 'B1'  From Dual Union All
          Select 'Mid1',  'M1'  From Dual Union All
          Select 'Top1',  'T1'  From Dual Union All
          Select 'Mid2',  'M2'  From Dual Union All
          Select 'Top2',  'T2'  From Dual Union All
          Select 'Top3',  'T3'  From Dual Union All
          Select 'Base2', 'B2'  From Dual 
        ),
-- PATHS - origin also irrelevant - there could be as many paths as you want
-- they don't have to be unique but should be consisted of IDs existing in the previous cte(tbl)
    tbl_or_view_or_cte_paths (A_PATH) AS
        (
          Select '/B1'        From Dual Union All
          Select '/B1/M1'     From Dual Union All
          Select '/B1/M2/T1'  From Dual Union All
          Select '/B1/M2'     From Dual Union All
          Select '/B2/M1/T2'  From Dual Union All
          Select '/B2/M1/T3'  From Dual Union All
          Select '/B2'        From Dual Union All
          --  some extra random paths below
          Select '/B1/M1/T1'     From Dual Union All
          Select '/B2/M2/T2'  From Dual Union All
          Select '/B2/M2/T3'  From Dual Union All
          Select '/M1/T1/T2/T3'  From Dual   -- one path with 4 elements
        )
SELECT
    (Select A_NAME From tbl Where ID = t.E_1) || 
        CASE WHEN t.ELEMENTS > 1 THEN '/' || (Select A_NAME From tbl Where ID = t.E_2) END || 
        CASE WHEN t.ELEMENTS > 2 THEN '/' || (Select A_NAME From tbl Where ID = t.E_3) END || 
        CASE WHEN t.ELEMENTS > 3 THEN '/' || (Select A_NAME From tbl Where ID = t.E_4) END "TXT"
FROM 
    ( Select    Length(t.A_PATH) - Length(Replace(A_PATH, '/', '')) "ELEMENTS",
                SubStr(A_PATH, InStr(t.A_PATH, '/', 1, 1) + 1, 2) "E_1",
                SubStr(A_PATH, CASE WHEN InStr(t.A_PATH, '/', 1, 2) != 0 THEN InStr(t.A_PATH, '/', 1, 2) + 1 END, 2) "E_2",
                SubStr(A_PATH, CASE WHEN InStr(t.A_PATH, '/', 1, 3) != 0 THEN InStr(t.A_PATH, '/', 1, 3) + 1 END, 2) "E_3",
                SubStr(A_PATH, CASE WHEN InStr(t.A_PATH, '/', 1, 4) != 0 THEN InStr(t.A_PATH, '/', 1, 4) + 1 END, 2) "E_4"
      From      tbl_or_view_or_cte_paths t
    ) t

TXT                   
-----------------------
Base1                   
Base1/Mid1              
Base1/Mid2/Top1         
Base1/Mid2              
Base2/Mid1/Top2         
Base2/Mid1/Top3         
Base2                   
Base1/Mid1/Top1         
Base2/Mid2/Top2         
Base2/Mid2/Top3         
Mid1/Top1/Top2/Top3