SQL> with tab as (
select 1 id, null parent_id, 'parent1' value from dual union all
select 2 id, 1 parent_id, 'child 1->2' value from dual union all
select 3 id, 2 parent_id, 'child 1->2->3' value from dual union all
select 4 id, 5 parent_id, 'child 5->4' value from dual union all
select 5 id, null parent_id, 'parent5' value from dual union all
select 6 id, null parent_id, 'parent6' value from dual union all
select 7 id, 4 parent_id, 'child 4->7' value from dual union all
select 8 id, null parent_id, 'parent8' value from dual union all
select 9 id, 1 parent_id, 'child 1->9' value from dual union all
select 10 id, 2 parent_id, 'child 1->2->10' value from dual
)
select first_value(id) over(partition by grp order by parent_id nulls first) ROOT_ID,
first_value(VALUE) over(partition by grp order by parent_id nulls first) ROOT_VALUE,
t.Id, t.parent_id, t.value
from (select t.*,
count(decode(parent_id, null, 1, null)) over(order by rownum) grp
from (select level lvl, t.*, sys_connect_by_path(id, '->')
from tab t
connect by prior id = parent_id
start with parent_id is null
) t
) t;
ROOT_ID ROOT_VALUE ID PARENT_ID VALUE
---------- -------------- ---------- ---------- --------------
1 parent1 1 parent1
1 parent1 2 1 child 1->2
1 parent1 9 1 child 1->9
1 parent1 3 2 child 1->2->3
1 parent1 10 2 child 1->2->10
5 parent5 5 parent5
5 parent5 7 4 child 4->7
5 parent5 4 5 child 5->4
6 parent6 6 parent6
8 parent8 8 parent8
10 rows selected
SQL> |