SELECT all the children of a record and concat

by gengencera   Last Updated October 12, 2018 02:06 AM

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 < 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,5   |
| 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


Related Questions


Updated February 05, 2018 08:06 AM

Updated July 03, 2016 08:02 AM

Updated February 24, 2017 12:06 PM

Updated August 28, 2017 14:06 PM

Updated August 02, 2018 06:06 AM