Hierarchical Queries

I've created some sort of 'dependencies' table that contain the entire dependencies of ours night process. The table looks like this: GRAND_MODEL | WAIT_4_MODEL_NAME test

Solution 1:

Oracle Setup:

CREATE TABLE table_name ( GRAND_MODEL, WAIT_4_MODEL_NAME ) AS
SELECT 'test',  'test1' FROM DUAL UNION ALL
SELECT 'test',  'test2' FROM DUAL UNION ALL
SELECT 'test',  'test3' FROM DUAL UNION ALL
SELECT 'test2', 'test3' FROM DUAL UNION ALL
SELECT 'test3', 'test4' FROM DUAL UNION ALL
SELECT 'test4', 'test5' FROM DUAL;

Query:

SELECT REGEXP_SUBSTR( tests, '[^|]+', 1, 1 ) AS first_model,
       REGEXP_SUBSTR( tests, '[^|]+', 1, 2 ) AS second_model,
       REGEXP_SUBSTR( tests, '[^|]+', 1, 3 ) AS third_model,
       REGEXP_SUBSTR( tests, '[^|]+', 1, 4 ) AS fourth_model,
       REGEXP_SUBSTR( tests, '[^|]+', 1, 5 ) AS fifth_model,
       REGEXP_SUBSTR( tests, '[^|]+', 1, 6 ) AS sixth_model,
       REGEXP_SUBSTR( tests, '[^|]+', 1, 7 ) AS seventh_model,
       REGEXP_SUBSTR( tests, '[^|]+', 1, 8 ) AS eighth_model,
       REGEXP_SUBSTR( tests, '[^|]+', 1, 9 ) AS ninth_model
FROM   (
  SELECT SYS_CONNECT_BY_PATH( wait_4_model_name, '|' ) || '|' ||  grand_model AS tests
  FROM  table_name
  CONNECT BY PRIOR grand_model = wait_4_model_name
);

Output:

FIRST_MODEL SECOND_MODEL THIRD_MODEL FOURTH_MODEL FIFTH_MODEL SIXTH_MODEL SEVENTH_MODEL EIGHTH_MODEL NINTH_MODEL
----------- ------------ ----------- ------------ ----------- ----------- ------------- ------------ -----------
test1       test                                                                                                 
test2       test                                                                                                 
test3       test                                                                                                 
test3       test2                                                                                                
test3       test2        test                                                                                    
test4       test3                                                                                                
test4       test3        test                                                                                    
test4       test3        test2                                                                                   
test4       test3        test2       test                                                                        
test5       test4                                                                                                
test5       test4        test3                                                                                   
test5       test4        test3       test                                                                        
test5       test4        test3       test2                                                                       
test5       test4        test3       test2        test                                                           

Solution 2:

Here is a solution that does not require string concatenation and re-split:

select    a.wait_4_model_name model1,
          a.grand_model       model2,
          b.grand_model       model3,
          c.grand_model       model4,
          d.grand_model       model5,
          e.grand_model       model6
from      DEL_SAGI_FOR_HIERARCHY a
left join DEL_SAGI_FOR_HIERARCHY b ON a.grand_model = b.wait_4_model_name
left join DEL_SAGI_FOR_HIERARCHY c ON b.grand_model = c.wait_4_model_name
left join DEL_SAGI_FOR_HIERARCHY d ON c.grand_model = d.wait_4_model_name
left join DEL_SAGI_FOR_HIERARCHY e ON d.grand_model = e.wait_4_model_name
where     a.wait_4_model_name not in (
              select grand_model from DEL_SAGI_FOR_HIERARCHY)
order by  1, 2, 3, 4, 5, 6

Output for the sample data is:

+--------+--------+--------+--------+--------+--------+
| MODEL1 | MODEL2 | MODEL3 | MODEL4 | MODEL5 | MODEL6 |
+--------+--------+--------+--------+--------+--------+
| test1  | test   |    -   |   -    |   -    |   -    |
| test5  | test4  | test3  | test   |   -    |   -    |
| test5  | test4  | test3  | test2  | test   |   -    |
+--------+--------+--------+--------+--------+--------+

Note that your sample data has no instances of models that do not have any dependencies at all, i.e. that do not need to wait for another model, nor are necessary for any models before they can start.