Forum Stats

  • 3,781,590 Users
  • 2,254,530 Discussions


How to split hierarchical data into levels dynamically

994126 Member Posts: 11
edited May 15, 2014 8:11AM in SQL & PL/SQL

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.........................




  • BluShadow
    BluShadow Member, Moderator Posts: 41,615 Red Diamond

    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_

    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
    BluShadow Member, Moderator Posts: 41,615 Red Diamond

    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
    994126 Member Posts: 11

    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
    BluShadow Member, Moderator Posts: 41,615 Red Diamond


    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.

This discussion has been closed.