lpad(' ',2*level-2) || sys_connect_by_path(lastname, '/') : sys_connect_by_path « Hierarchical Query « Oracle PL / SQL

1. Oracle PL / SQL
2. Hierarchical Query
3. sys_connect_by_path

lpad(' ',2*level-2) || sys_connect_by_path(lastname, '/')

  
create table emp(
         no                 integer         primary key
        ,name             varchar2(20)    not null
        ,mgr               integer        
);

insert into emp(no, name, mgr)
        values (1,'Tom', 2);
        
insert into emp(no, name, mgr)
        values (2,'Jack',3);
insert into emp(no, name, mgr)
        values (3,'Jane',4);
insert into emp(no, name, mgr)
        values (4,'Jason',5);


select lpad(' ',2*level-2) || sys_connect_by_path(lastname, '/') as path
    from emp
    start with emp_no = 2
    connect by prior emp_no = mgr;

PATH                                                                            
--------------------------
/Jack                                                                           
  /Jack/Tom                                                                     

2 rows selected.

drop table emp;

   
  

Related examples in the same category