Recursive query in Oracle to find children and sibling

Question

I'm struggling a hierarchical SQL query. I want to have another 2 columns of the disp_order of its children and sibling.

Children - Should hold all disp_order of their child and their grand children and so far.

Sibling - Should hold the disp_order of the row having the same parent.

+------------+-----+-------------+--------+
| disp_order | lvl | description | parent |
+------------+-----+-------------+--------+
|     0      |  1  |      A      |        |
|     1      |  2  |      B      |    0   |
|     2      |  3  |      C      |    1   |
|     3      |  4  |      D      |    2   |
|     4      |  5  |      E      |    3   |
|     5      |  2  |      F      |    0   |
|     6      |  3  |      G      |    5   |
|     7      |  3  |      H      |    5   |
|     8      |  3  |      I      |    5   |
|     9      |  4  |      J      |    8   |
|     10     |  5  |      K      |    9   |
+------------+-----+-------------+--------+

What the result should be:

+------------+-----+-------------+--------+------------------------+---------+
| disp_order | lvl | description | parent |        children        | sibling |
+------------+-----+-------------+--------+------------------------+---------+
|     0      |  1  |      A      |        |   1,2,3,4,5,6,7,8,9,10 |         |
|     1      |  2  |      B      |    0   |          2,3,4         |    5    |
|     2      |  3  |      C      |    1   |           3,4          |         |
|     3      |  4  |      D      |    2   |            4           |         |
|     4      |  5  |      E      |    3   |                        |         |
|     5      |  2  |      F      |    0   |        6,7,8,9,10      |    1    |
|     6      |  3  |      G      |    5   |                        |   7,8   |
|     7      |  3  |      H      |    5   |                        |   6,8   |
|     8      |  3  |      I      |    5   |           9,10         |   6,7   |
|     9      |  4  |      J      |    8   |            10          |         |
|     10     |  5  |      K      |    9   |                        |         |
+------------+-----+-------------+--------+------------------------+---------+

Here is my current query:

SELECT t.*,
       ( SELECT MAX( disp_order )
         FROM   tbl_pattern p
         WHERE  p.lvl        = t.lvl - 1
         AND    p.disp_order < t.disp_order ) AS parent
FROM   tbl_pattern t

score 3 · Accepted Answer · answered Oct 12 '18 at 09:10

Continuing from your previous question:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE tbl_pattern ( order_no, code, disp_order, lvl, description ) AS
SELECT 'RM001-01', 1, 0, 1, 'HK140904-1A' FROM DUAL UNION ALL
SELECT 'RM001-01', 1, 1, 2, 'HK140904-1B' FROM DUAL UNION ALL
SELECT 'RM001-01', 1, 2, 3, 'HK140904-1B' FROM DUAL UNION ALL
SELECT 'RM001-01', 1, 3, 4, 'HK140904-1C' FROM DUAL UNION ALL
SELECT 'RM001-01', 1, 4, 5, 'HK140904-1D' FROM DUAL UNION ALL
SELECT 'RM001-01', 1, 5, 2, 'HK140904-1E' FROM DUAL UNION ALL
SELECT 'RM001-01', 1, 6, 3, 'HK140904-1E' FROM DUAL UNION ALL
SELECT 'RM001-01', 1, 7, 3, 'HK140904-1X' FROM DUAL UNION ALL
SELECT 'RM001-01', 1, 8, 4, 'HK140904-1E' FROM DUAL UNION ALL
SELECT 'RM001-01', 1, 9, 5, 'HK140904-1E' FROM DUAL;

Query 1:

WITH data ( order_no, code, disp_order, lvl, description, parent ) AS (
  SELECT t.*,
         ( SELECT MAX( disp_order )
           FROM   tbl_pattern p
           WHERE  p.order_no   = t.order_no
           AND    p.code       = t.code
           AND    p.lvl        = t.lvl - 1
           AND    p.disp_order < t.disp_order ) AS parent
  FROM   tbl_pattern t
)
SELECT d.*,
       ( SELECT LISTAGG( c.disp_order, ',' ) WITHIN GROUP ( ORDER BY c.disp_order )
         FROM   data c
         START WITH c.parent = d.disp_order
         AND        c.order_no = d.order_no
         AND        c.code     = d.code
         CONNECT BY PRIOR c.disp_order = c.parent
         AND        PRIOR c.order_no   = c.order_no
         AND        PRIOR c.code       = c.code 
       ) AS children,
       ( SELECT LISTAGG( c.disp_order, ',' ) WITHIN GROUP ( ORDER BY c.disp_order )
         FROM   data c
         WHERE  c.parent     = d.parent
         AND    c.disp_order <> d.disp_order
         AND    c.order_no   = d.order_no
         AND    c.code       = d.code
       ) AS siblings
FROM   data d

Results:

| ORDER_NO | CODE | DISP_ORDER | LVL | DESCRIPTION | PARENT |          CHILDREN | SIBLINGS |
|----------|------|------------|-----|-------------|--------|-------------------|----------|
| RM001-01 |    1 |          0 |   1 | HK140904-1A | (null) | 1,2,3,4,5,6,7,8,9 |   (null) |
| RM001-01 |    1 |          1 |   2 | HK140904-1B |      0 |             2,3,4 |        5 |
| RM001-01 |    1 |          2 |   3 | HK140904-1B |      1 |               3,4 |   (null) |
| RM001-01 |    1 |          3 |   4 | HK140904-1C |      2 |                 4 |   (null) |
| RM001-01 |    1 |          4 |   5 | HK140904-1D |      3 |            (null) |   (null) |
| RM001-01 |    1 |          5 |   2 | HK140904-1E |      0 |           6,7,8,9 |        1 |
| RM001-01 |    1 |          6 |   3 | HK140904-1E |      5 |            (null) |        7 |
| RM001-01 |    1 |          7 |   3 | HK140904-1X |      5 |               8,9 |        6 |
| RM001-01 |    1 |          8 |   4 | HK140904-1E |      7 |                 9 |   (null) |
| RM001-01 |    1 |          9 |   5 | HK140904-1E |      8 |            (null) |   (null) |

Oh my God! Thank you! You're a life saver. I'd give you a thousand points if I could — gengencera, Oct 12 '18 at 09:13

Recursive query in Oracle to find children and sibling

1 Answers1

Linked