sagi sagi - 7 months ago 10
SQL Question

How to get the path of an hierarchy table

I've been struggling a bit about how to deal with this situation:

I have a table structured as follow:

Family_code | Parent_Family_Code | ....
1 2
2 4
3 6
4 3
......................


When a user is searching for a specific family code, I need to return the entire path (up to 10 levels max) , so for example for family_code = 1 I'll need:

Family_code | parent_1 | p_2 | p_3 | p_4 | p_5 | .....
1 2 4 3 6 null null.....


I know I can use
sys_connect_by_path()
which will bring me the expected result but as a string, and not as separate columns which is something I'll prefer to avoid.

This can also be done with 10 left joins to the same table, or the use of
LEAD()/LAG()
functions which will include a lot of sub queries and will make a messy and unreadable query, but then again, this will be more heavy then it should be and I need to simplify it as I can.

I've come up with a solution using
substr()
function(the length of the codes will always be varchar2(3)):

SELECT s.family_code,
s.parent_family_code_1,
s.parent_family_code_2,
CASE WHEN length(s.family_path) - (4 * 3 + 2) > 0 THEN substr(s.family_path, length(s.family_path) - (4 * 3 + 2), 3) ELSE NULL END as parent_family_code_3,
CASE WHEN length(s.family_path) - (4 * 4 + 2) > 0 THEN substr(s.family_path, length(s.family_path) - (4 * 4 + 2), 3) ELSE NULL END as parent_family_code_4,
CASE WHEN length(s.family_path) - (4 * 5 + 2) > 0 THEN substr(s.family_path, length(s.family_path) - (4 * 5 + 2), 3) ELSE NULL END as parent_family_code_5,
CASE WHEN length(s.family_path) - (4 * 6 + 2) > 0 THEN substr(s.family_path, length(s.family_path) - (4 * 6 + 2), 3) ELSE NULL END as parent_family_code_6,
CASE WHEN length(s.family_path) - (4 * 7 + 2) > 0 THEN substr(s.family_path, length(s.family_path) - (4 * 7 + 2), 3) ELSE NULL END as parent_family_code_7,
CASE WHEN length(s.family_path) - (4 * 8 + 2) > 0 THEN substr(s.family_path, length(s.family_path) - (4 * 8 + 2), 3) ELSE NULL END as parent_family_code_8,
CASE WHEN length(s.family_path) - (4 * 9 + 2) > 0 THEN substr(s.family_path, length(s.family_path) - (4 * 9 + 2), 3) ELSE NULL END as parent_family_code_9,
CASE WHEN length(s.family_path) - (4 * 10 + 2) > 0 THEN substr(s.family_path, length(s.family_path) - (4 * 10 + 2), 3) ELSE NULL END as parent_family_code_10
FROM (SELECT t.family_code,
t.parent_family_code as parent_family_code_1,
prior t.parent_family_code as parent_family_code_2,
sys_connect_by_path(t.family_code, ',') as family_path
FROM table t
connect by prior t.family_code = t.parent_family_code) s


But I would like a solution without the use of substrings since it will be harder to do any maintaince on it when other developers will touch it
.
So basically my question is - how do I select the entire path as different columns without the use of substrings?

Answer

A slightly modified query from @MT0 answer, using PIVOT clause.

SELECT * 
FROM (
    select connect_by_root( family_code ) as Family_code, 
           'P_' || level lev_el,  
           parent_family_code
    from table_name t
    start with not exists(
        select 1 from table_name t1
        where t.family_code = t1.parent_family_code )
    connect by prior parent_family_code =  family_code
)
PIVOT (
  max( parent_family_code ) 
  FOR (lev_el) IN ( 
       'P_1', 'P_2', 'P_3', 'P_4', 'P_5', 'P_6','P_7', 'P_8','P_9','P_10' ,
       'P_11', 'P_12', 'P_13', 'P_14', 'P_15', 'P_16','P_17', 'P_18','P_19','P_20',
       'P_21', 'P_22', 'P_23', 'P_24', 'P_25', 'P_26','P_27', 'P_28','P_29','P_30' 
       /* add more "levels" here if required */
)
);

A result of the query for sample data from @MT0 answer (@MT0, thank you for providing sample data):

    FAMILY_CODE      'P_1'      'P_2'      'P_3'      'P_4'      'P_5'      'P_6'      'P_7'      'P_8'      'P_9'     'P_10'     'P_11'     'P_12'     'P_13'     'P_14'     'P_15'     'P_16'     'P_17'     'P_18'     'P_19'     'P_20'     'P_21'     'P_22'     'P_23'     'P_24'     'P_25'     'P_26'     'P_27'     'P_28'     'P_29'     'P_30'
--------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
              1          2          4          5          6                                                                                                                                                                                                                                                                                              
              8          7          9         10         11