Ideally you need to fix your data model and data, as you're storing it very badly. You really want to store just parent-child values, and not the actual hierarchical paths in the data.
So, with your data you can extract the parent from the path using regular expressions. e.g.
Wrote file afiedt.buf
1 select regexp_substr('/King/Russell/Tucker/','[^/]+',1,regexp_count('/King/Russell/Tucker','/')-1)
2* from dual
or you could do it with normal string manipulation using instr and substr functions.
As for getting your result, a fixed query cannot have a dynamic projection (number of columns as the columns must be known when the query is parsed and that happens before any data is fetched... so by definition... the columns of a query cannot be based on the data it's going to fetch.
The only way to achieve that is to use dynamic queries, meaning you'll have to write code that queries the data first to determine how many levels (columns) you're going to need, then generate a dynamic sql string based on that, and then execute that dynamic SQL to get the result.
Of course, because the SQL is going to be dynamic, you cannot have any static code to process it, so you'd have to have something like DBMS_SQL package process the results, or pass the query back as a ref cursor to a 3rd party application which can dynamically obtain the results.
An alternative is, if you know the maximum number of levels there can possibly be, you write a static query to assume that many levels and then it just queries data into the columns as far as it needs to, maybe leaving some of the columns blank if there's no data for them yet.
Here's a static query that just extracts the levels out of the path information...
Wrote file afiedt.buf
1 with t(employee,cycle,lvl,path) as (
2 select 'Russell', 1, 2, '/King/Russell' from dual union all
3 select 'Tucker', 0, 3, '/King/Russell/Tucker' from dual union all
4 select 'Bernstein', 0, 3, '/King/Russell/Bernstein' from dual union all
5 select 'Hall', 0, 3, '/King/Russell/Hall' from dual union all
6 select 'Olsen', 0, 3, '/King/Russell/Olsen' from dual union all
7 select 'Cambrault', 0, 3, '/King/Russell/Cambrault' from dual union all
8 select 'Tuvault', 0, 3, '/King/Russell/Tuvault' from dual union all
9 select 'Partners', 0, 2, '/King/Partners' from dual union all
10 select 'King', 0, 3, '/King/Russell/King' from dual union all
11 select 'Sully', 0, 3, '/King/Russell/Sully' from dual union all
12 select 'McEwen', 0, 3, '/King/Russell/McEwen' from dual
15 -- end of test data
17 select regexp_substr(path, '[^/]+',1,1) as level1
18 ,regexp_substr(path, '[^/]+',1,2) as level2
19 ,regexp_substr(path, '[^/]+',1,3) as level3
20 ,regexp_substr(path, '[^/]+',1,4) as level4
21 ,regexp_substr(path, '[^/]+',1,5) as level5
22 ,regexp_substr(path, '[^/]+',1,6) as level6
23 from t
24* order by 1,2,3,4,5,6
LEVEL1 LEVEL2 LEVEL3 LEVEL4 LEVEL5 LEVEL6
----------------------- ----------------------- ----------------------- ----------------------- ----------------------- -----------------------
King Russell Bernstein
King Russell Cambrault
King Russell Hall
King Russell King
King Russell McEwen
King Russell Olsen
King Russell Sully
King Russell Tucker
King Russell Tuvault
11 rows selected.
What about my first reply didn't you understand?
What have you tried yourself to achieve this dynamic requirement?
What are you going to do with the dynamic columns of data you get from your dynamic query?
I suggest you read about dynamic pivoting in the links in the FAQ: Re: 4. How do I convert rows to columns?
(yes it is relevant)
especially the article on PL/SQL 101 : Cursors and SQL Projection
SQL queries by their very nature do not allow for a dynamic number of columns to be returned, it has to be fixed at some point before the data is fetched, even if the query itself is created dynamically by querying the data once beforehand.
And you have to recognise that your data is badly stored... it isn't being stored properly as hierarchical data... rather it is flat data that is duplicating the full path of parent information within it. That's not how you should store hierarchical data.