create table t_group (
group_uid number not null,
group_name varchar2(1000),
parent_uid number,
constraint t_group primary key ( group_uid )
)
insert into t_group values ( 1, 'company', NULL );
insert into t_group values ( 2, 'marketing', 1 );
insert into t_group values ( 3, 'sales', 1 );
insert into t_group values ( 4, 'sales1', 3 );
insert into t_group values ( 5, 'sales2', 3 );
insert into t_group values ( 6, 'sales3', 3 );
insert into t_group values ( 7, 'company2', NULL );
insert into t_group values ( 8, 'marketing', 7 );
insert into t_group values ( 9, 'sales', 7 );
insert into t_group values ( 10, 'marketing1', 2 );
select LPAD(' ', 4*(LEVEL-1)) || group_name from t_group
connect by prior group_uid = parent_uid
start with parent_uid is null
order siblings by group_name
LPAD('',4*(LEVEL-1))||GROUP_NAME
--------------------------------------------------------------------------------
company
marketing
marketing1
sales
sales1
sales2
sales3
company2
marketing
sales
10 rows selected.
