Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE MY_TREE
(ID_NAME VARCHAR2(1) PRIMARY KEY,
 PARENT_ID VARCHAR2(1),
 PARENT_DISTANCE NUMBER(2)
);
INSERT ALL
INTO MY_TREE (ID_NAME,PARENT_ID,PARENT_DISTANCE) VALUES('A',NULL,NULL)
 INTO MY_TREE (ID_NAME,PARENT_ID,PARENT_DISTANCE) VALUES('B','A',1)
 INTO MY_TREE (ID_NAME,PARENT_ID,PARENT_DISTANCE) VALUES('C','B',3)
 INTO MY_TREE (ID_NAME,PARENT_ID,PARENT_DISTANCE) VALUES('D','B',5)
 INTO MY_TREE (ID_NAME,PARENT_ID,PARENT_DISTANCE) VALUES('E','C',7)
 INTO MY_TREE (ID_NAME,PARENT_ID,PARENT_DISTANCE) VALUES('F','D',11)
 INTO MY_TREE (ID_NAME,PARENT_ID,PARENT_DISTANCE) VALUES('G','D',13)
 select 1 from dual
7 rows affected

WITH brumba(le_vel,root,node,id_name,root_distance) AS (
  SELECT 1 as le_vel, id_name as root, null as node, id_name, to_number(null) as root_distance  
  FROM MY_TREE WHERE parent_id IS NULL
  UNION ALL
  SELECT b.le_vel + 1, b.root, 
         CASE WHEN 1 < (
                SELECT count(*) FROM MY_TREE t1 WHERE t1.parent_id = t.parent_id
              )
              THEN t.parent_id ELSE b.node
         END,
         t.id_name, coalesce(b.root_distance,0)+t.parent_distance
  FROM MY_TREE t
  JOIN brumba b ON b.id_name = t.parent_id
)
SELECT * FROM brumba


LE_VEL
ROOT
NODE
ID_NAME
ROOT_DISTANCE


1
A
null
A
null

2
A
null
B
1

3
A
B
C
4

3
A
B
D
6

4
A
B
E
11

4
A
D
F
17

4
A
D
G
19


CREATE TABLE my_result AS
WITH brumba(le_vel,root,node,id_name,root_distance) AS (
  SELECT 1 as le_vel, id_name as root, null as node, id_name, to_number(null) as root_distance  
  FROM MY_TREE WHERE parent_id IS NULL
  UNION ALL
  SELECT b.le_vel + 1, b.root, 
         CASE WHEN 1 < (
                SELECT count(*) FROM MY_TREE t1 WHERE t1.parent_id = t.parent_id
              )
              THEN t.parent_id ELSE b.node
         END,
         t.id_name, coalesce(b.root_distance,0)+t.parent_distance
  FROM MY_TREE t
  JOIN brumba b ON b.id_name = t.parent_id
)
SELECT * FROM brumba
7 rows affected

SELECT * FROM My_result


LE_VEL
ROOT
NODE
ID_NAME
ROOT_DISTANCE


1
A
null
A
null

2
A
null
B
1

3
A
B
C
4

3
A
B
D
6

4
A
B
E
11

4
A
D
F
17

4
A
D
G
19