CREATE TABLE b(
ID VARCHAR2(50 BYTE),
PARENT_ID VARCHAR2(50 BYTE),
NAME NVARCHAR2(200)
);
insert into b values ('401.1.1','401.1','a');
insert into b values ( '402.1.1','402.1','d');
insert into b values ('402.1.1.1','402.1','e');
insert into b values ('400','1','asc');
insert into b values( '401','400','dep1');
insert into b values ( '402','400','dep2');
insert into b values('403','400','dep3');
insert into b values('401.1','401','A');
insert into b values('401.2','401','B');
insert into b values('401.3','401','C');
insert into b values('402.1','402','D');
insert into b values('402.2','402','E');
NOW I WANT MY RESULT WOULD BE LIKE THIS
| Header 1 | Header 2 | Header 3 | Header 4 | Header 5 | Header 6 | Header 7 | Header 8 | Header 9 | Header 10 | Header 11 | Header 12 | Header 13 | Header 14 | Header 14 |
|---|
| 400 | 1 | asc | | | | | | | | | | | | |
| 400 | 1 | asc | 401 | 400 | dep1 | | | | | | | | | |
| 400 | 1 | asc | 402 | 400 | dep2 | | | | | | | | | |
| 400 | 1 | asc | 403 | 400 | dep3 | | | | | | | | | |
| 400 | 1 | asc | 401 | 400 | dep1 | 401.1 | 401 | A | | | | | | |
| 400 | 1 | asc | 401 | 400 | dep1 | 401.2 | 401 | B | | | | | | |
| 400 | 1 | asc | 401 | 400 | dep1 | 401.3 | 401 | C | | | | | | |
| 400 | 1 | asc | 402 | 400 | dep2 | 401.1 | 402 | D | | | | | | |
| 400 | 1 | asc | 402 | 400 | dep2 | 401.2 | 402 | E | | | | | | |
| 400 | 1 | asc | 401 | 400 | dep1 | 401.1 | 401 | A | 401.1.1 | 401.1 | a | | | |
| 400 | 1 | asc | 402 | 400 | dep2 | 401.1 | 402 | D | 402.1.1 | 402.1 | d | | | |
| 400 | 1 | asc | 402 | 400 | dep2 | 401.1 | 402 | D | 402.1.1 | 402.1 | d | 402.1.1.1 | 402.1.1 | e |
i use self join and union all bu retrieve this information ,but in other case my hiearchy tree has more than 10 sub department then my script is not correct ,please give solution not only for 4 subdepartment like this ,but nearly 10 ten subdepartment,because in other offices as i said i have 10 subdepartment ,here i only give example