Oracle Hierarchical Query to get different levels into different columns

Question

I've source data Like this

Childid | Parent ID
------- | ---------
1 | NULL
2 | 1
3 | 1
4 | 2
5 | 4
6 | 5
7 | 6

I need an oracle query to show the out put like this.

Child | L1Parent | l2Parent | L3Parent | L4Parent | L5Parent
----- | -------- | -------- | -------- | -------- | --------
1 | NULL
2 | 1
3 | 1
4 | 1 | 2
5 | 1 | 2 | 4
6 | 1 | 2 | 4 | 5
7 | 1 | 2 | 4 | 5 | 6

unknown level of parents right? (dynamic SQL likely needed) and does it have to be separate columns? SYS_CONNECT_BY_PATH(parentID, '/') "Path" seems like it would work well here... docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm — xQbert
– xQbert, Commented Apr 28, 2017 at 19:26
there is a cycle in ths hierarchy, childid = 5 has parent=5 ==> the query , that is - this child is it's own parent. — krokodilko
– krokodilko, Commented Apr 28, 2017 at 19:45
You will be best off assuming a maximum number of levels that your query will support, so that the number and names of the columns in your result set will known ahead of time. That makes it easier to do in Oracle and easier for your clients (whatever is submitting this query) to process. — Matthew McPeak
– Matthew McPeak, Commented Apr 28, 2017 at 19:48

Petr · Accepted Answer · 2017-11-30 09:31:38Z

7

This seems to me easier:

SELECT childId,
       trim(SYS_CONNECT_BY_PATH(decode(level,2,parentId,''), ' ')) AS L1,
       trim(SYS_CONNECT_BY_PATH(decode(level,3,parentId,''), ' ')) AS L2,
       trim(SYS_CONNECT_BY_PATH(decode(level,4,parentId,''), ' ')) AS L3,
       trim(SYS_CONNECT_BY_PATH(decode(level,5,parentId,''), ' ')) AS L4,
       trim(SYS_CONNECT_BY_PATH(decode(level,6,parentId,''), ' ')) AS L5,
       trim(SYS_CONNECT_BY_PATH(decode(level,7,parentId,''), ' ')) AS L6,
       trim(SYS_CONNECT_BY_PATH(decode(level,8,parentId,''), ' ')) AS L7
FROM table_name
START WITH parentId is null
CONNECT BY PRIOR childId = parentId;

answered Nov 30, 2017 at 9:31

Petr

711 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Paulo Pedroso Over a year ago

This one saved my life. Although I had to adapt it in order to retrieve text data instead of number. I had to do this: trim(BOTH '>' FROM SYS_CONNECT_BY_PATH(decode(level,1,NAME,''), '>')) AS NAME_1

Dorababu G Over a year ago

Oracle SQL is Awesome. Complex requirement can be implemented with one SQL statement.

MT0 · Accepted Answer · 2017-04-28 23:25:52Z

Oracle Setup:

CREATE TABLE table_name ( childId, parentId ) AS
  SELECT 1, NULL FROM DUAL UNION ALL
  SELECT 2, 1    FROM DUAL UNION ALL
  SELECT 3, 1    FROM DUAL UNION ALL
  SELECT 4, 2    FROM DUAL UNION ALL
  SELECT 5, 4    FROM DUAL UNION ALL
  SELECT 6, 5    FROM DUAL UNION ALL
  SELECT 7, 6    FROM DUAL;

Query:

SELECT childId,
       CASE WHEN p02 = 1 THEN NULL WHEN p03 = 1 THEN SUBSTR( path, p02 ) ELSE SUBSTR( path, p02, p03 - p02 - 1 ) END AS Lp1,
       CASE WHEN p03 = 1 THEN NULL WHEN p04 = 1 THEN SUBSTR( path, p03 ) ELSE SUBSTR( path, p03, p04 - p03 - 1 ) END AS lp2,
       CASE WHEN p04 = 1 THEN NULL WHEN p05 = 1 THEN SUBSTR( path, p04 ) ELSE SUBSTR( path, p04, p05 - p04 - 1 ) END AS lp3,
       CASE WHEN p05 = 1 THEN NULL WHEN p06 = 1 THEN SUBSTR( path, p05 ) ELSE SUBSTR( path, p05, p06 - p05 - 1 ) END AS lp4,
       CASE WHEN p06 = 1 THEN NULL WHEN p07 = 1 THEN SUBSTR( path, p06 ) ELSE SUBSTR( path, p06, p07 - p06 - 1 ) END AS lp5,
       CASE WHEN p07 = 1 THEN NULL WHEN p08 = 1 THEN SUBSTR( path, p07 ) ELSE SUBSTR( path, p07, p08 - p07 - 1 ) END AS lp6
FROM   (
  SELECT childId,
         path,
         INSTR( path, '/', 1,  2 ) + 1 AS p02,
         INSTR( path, '/', 1,  3 ) + 1 AS p03,
         INSTR( path, '/', 1,  4 ) + 1 AS p04,
         INSTR( path, '/', 1,  5 ) + 1 AS p05,
         INSTR( path, '/', 1,  6 ) + 1 AS p06,
         INSTR( path, '/', 1,  7 ) + 1 AS p07,
         INSTR( path, '/', 1,  8 ) + 1 AS p08
  FROM   (
    SELECT childId,
           SYS_CONNECT_BY_PATH( parentId, '/') AS path
    FROM   table_name
    START WITH parentId IS NULL
    CONNECT BY PRIOR childId = parentId
  )
);

Output:

   CHILDID LP1 LP2 LP3 LP4 LP5 LP6
---------- --- --- --- --- --- ---
         1                        
         2 1                      
         4 1   2                  
         5 1   2   4              
         6 1   2   4   5          
         7 1   2   4   5   6      
         3 1

Query 2 - Regular Expressions:

SELECT childId,
       TO_NUMBER( REGEXP_SUBSTR( path, '/([^/]*)', 1, 2, NULL, 1 ) ) AS lp1,
       TO_NUMBER( REGEXP_SUBSTR( path, '/([^/]*)', 1, 3, NULL, 1 ) ) AS lp2,
       TO_NUMBER( REGEXP_SUBSTR( path, '/([^/]*)', 1, 4, NULL, 1 ) ) AS lp3,
       TO_NUMBER( REGEXP_SUBSTR( path, '/([^/]*)', 1, 5, NULL, 1 ) ) AS lp4,
       TO_NUMBER( REGEXP_SUBSTR( path, '/([^/]*)', 1, 6, NULL, 1 ) ) AS lp5,
       TO_NUMBER( REGEXP_SUBSTR( path, '/([^/]*)', 1, 7, NULL, 1 ) ) AS lp6,
       TO_NUMBER( REGEXP_SUBSTR( path, '/([^/]*)', 1, 8, NULL, 1 ) ) AS lp7
FROM   (
  SELECT childId,
         SYS_CONNECT_BY_PATH( parentId, '/') AS path
  FROM   table_name
  START WITH parentId IS NULL
  CONNECT BY PRIOR childId = parentId
);

Collectives™ on Stack Overflow

Oracle Hierarchical Query to get different levels into different columns

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related