How to split hierarchical data into levels dynamically

994126

Ex: My actual data like this

Employee                    Cycle       LEVEL                   Path

-------------------------            ------       ------              -------------------------

Russell                          1             2                   /King/Russell

Tucker                           0             3                   /King/Russell/Tucker

Bernstein                       0             3                    /King/Russell/Bernstein

Hall                               0             3                  /King/Russell/Hall

Olsen                            0             3                  /King/Russell/Olsen

Cambrault                     0              3                   /King/Russell/Cambrault

Tuvault                         0               3                   /King/Russell/Tuvault

Partners                       0               2                    /King/Partners

King                            0               3                   /King/Partners/King

Sully                          0                3                     /King/Partners/Sully

McEwen                      0               3                   /King/Partners/McEwen

But we want split this hierarchical path data into levels dynamically like............

level1                    level2             level3

--------                  --------------      ----------------

King                    Russell        

King                    Russell          Truker

King                    Russell         Bernstein

King                    Russell         Hall

King                   Russel           Olsen

King                   Russel           Cambrault

King                   Russel          Tuvault

King                 Partners

so on.........................

Thanks

Goal

BluShadow

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.

SQL> ed
Wrote file afiedt.buf
  1  select regexp_substr('/King/Russell/Tucker/','[^/]+',1,regexp_count('/King/Russell/Tucker','/')-1)
  2* from dual
SQL> /
REGEXP_
-------
Russell

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.

BluShadow

Here's a static query that just extracts the levels out of the path information...

SQL> ed
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
 13    )
 14  --
 15  -- end of test data
 16  --
 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
SQL> /

LEVEL1                  LEVEL2                  LEVEL3                  LEVEL4                  LEVEL5                  LEVEL6
----------------------- ----------------------- ----------------------- ----------------------- ----------------------- -----------------------
King                    Partners
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
King                    Russell

11 rows selected.

994126

Thanks for replied my issue

But we want split hierarchical data into levels dynamically.

I know about REGEXP_SUBSTR. We don't want static type.

I hope you understand our issue

BluShadow

So

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:

(yes it is relevant)

especially the article on

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.