How to populate the hierarchy data of below table in flat format like below:
| Source table data |
| Child | Parent |
| MANK |
| KAN | MANK |
| AWI | KAN |
| MAN | KAN |
| AM | KAN |
| MISTI | AWI |
| RAS | MANK |
Target table data: |
| LEVEL1 | LEVEL2 | LEVEL3 | LEVEL4 | LEVEL5 |
| MANK | KAN | AWI | MISTI | MISTI |
| MANK | KAN | AWI | AWI | AWI |
| MANK | KAN | MAN | MAN | MAN |
| MANK | KAN | AM | AM | AM |
| MANK | MANK | MANK | MANK | MANK |
| MANK | KAN | KAN | KAN | KAN |
I have use the below code but it produces null values as well:
select substr(regexp_substr(scbp, '-[^-]*', 1, 1),2) as l1,
substr(regexp_substr(scbp, '-[^-]*', 1, 2),2) as l2,
substr(regexp_substr(scbp, '-[^-]*', 1, 3),2) as l3,
substr(regexp_substr(scbp, '-[^-]*', 1, 4),2) as l4,
substr(regexp_substr(scbp, '-[^-]*', 1, 5),2) as l5,
substr(regexp_substr(scbp, '-[^-]*', 1, 6),2) as l6
from (
select sys_connect_by_path(child, '-') scbp
from test_7nov18
start with parent is null
connect by prior child = parent
);