SELECT all the children of a row and concat I have this requirement that I need to attach a new column in select statement. It holds a string series of the disp_order of its children.

I currently have the SQL statement only for the immediate parent only.

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


Here is the table from the said SQL statement query:

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


From the example, the result for the children column should be like this:

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


Data representation through hierarchy of sample provided:

1 - HK140904-1A
  └2 - HK140904-1B
    └3 - HK140904-1C
      └4 - HK140904-1D
        └5 - HK140904-1E
  └2 - HK140904-1F
    └3 - HK140904-1G
    └3 - HK140904-1H
      └4 - HK140904-1I
        └5 - HK140904-1J
For those who encountered the same problem. I did not answer this but someone provided the answer, I'll leave the link here: https://stackoverflow.com/questions/52773824/recursive-query-in-oracle-to-find-children-and-sibling/52776124#52776124