title: Recursive query in Oracle to find children and sibling
body: <p>I'm struggling a hierarchical SQL query. I want to have another 2 columns of the disp_order of its children and sibling.</p>
  
  <p><strong>Children</strong> - Should hold all disp_order of their child and their grand children and so far.</p>
  
  <p><strong>Sibling</strong> - Should hold the disp_order of the row having the same parent.</p>
  
  <pre><code>+------------+-----+-------------+--------+
  | 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   |
  +------------+-----+-------------+--------+
  </code></pre>
  
  <p>What the result should be:</p>
  
  <pre><code>+------------+-----+-------------+--------+------------------------+---------+
  | 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   |                        |         |
  +------------+-----+-------------+--------+------------------------+---------+
  </code></pre>
  
  <p>Here is my current query:</p>
  
  <pre><code>SELECT t.*,
         ( SELECT MAX( disp_order )
           FROM   tbl_pattern p
           WHERE  p.lvl        = t.lvl - 1
           AND    p.disp_order &lt; t.disp_order ) AS parent
  FROM   tbl_pattern t
  </code></pre>