Input

SQL

select distinct id, client-level, root-id, path
from
(
  select 
    w1.id, 
    level as client-level, 
    CONNECT_BY_ROOT w1.id as root-id,
    SYS_CONNECT_BY_PATH((w1.id ) || '|' , '=>') path 
  from 
    (
      select * 
      from table-name 
      where ultimate_parent_id='0516013318'
    ) w1
  start with w1.id = w1.ultimate_parent_id
  connect by nocycle prior w1.id = w1.parent_id
) w2
order by root-id asc, client-level asc;

-- Oracle selects the root row(s) of the hierarchy (those rows that satisfy the START WITH condition)

-- Oracle selects the child rows of each root row. Each child row must satisfy the condition of the CONNECT BY condition with respect to one of the root rows.

-- to find the children of a parent row, Oracle evaluates the PRIOR expression of the CONNECT BY condition for the parent row and the other expression for each row in the table.

-- for example, first cycle, in this case, find all rows that parent-eci = 0516013318

Output