4 Replies Latest reply: May 15, 2014 12:11 PM by BluShadow RSS

    How to split hierarchical data into levels dynamically


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




        • 1. Re: How to split hierarchical data into levels dynamically

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



          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.

          • 2. Re: How to split hierarchical data into levels dynamically

            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.


            • 3. Re: How to split hierarchical data into levels dynamically

              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

              • 4. Re: How to split hierarchical data into levels dynamically


                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.