Oracle sql recursive

Question

I have the following table:

+----+-----------+------+
| ID | Parent_ID | Name |
+----+-----------+------+
|  1 | null      | A    |
|  2 | null      | B    |
|  3 | null      | C    |
|  4 | 1         | D    |
|  5 | 4         | E    |
|  6 | 2         | F    |
+----+-----------+------+

And I need to get table like this:

+----+------+
| ID | Name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
|  4 | AD   |
|  5 | ADE  |
|  6 | BF   |
+----+------+

I checked the ORACLE related questions here, but I did't find anything useful.

It is not clear what you are trying to achieve. Nor have you stated what you have attempted and where it's gone wrong. — Ceisc, Nov 23 '17 at 09:48

score 2 · Answer 1 · answered Nov 23 '17 at 09:50

2

Try this.

SELECT ID, REPLACE (SYS_CONNECT_BY_PATH (NAME, ' '), ' ')
      FROM TABLE1
START WITH PARENT_ID IS NULL
CONNECT BY PRIOR ID = PARENT_ID
  ORDER BY ID;

SQL Fiddle

answered Nov 23 '17 at 09:50

Kaushik Nayak

30,772
5
32
45

score 0 · Answer 2 · edited Nov 23 '17 at 09:58

0

Try this

WITH b(ID, Parent_ID) AS (
  -- Anchor member.
  SELECT ID,
         Parent_ID
  FROM   DataTable
  WHERE  Parent_ID IS NULL
  UNION ALL
  -- Recursive member.
  SELECT a.ID,
         a.Parent_ID
  FROM   DataTable a, b
  WHERE  a.Parent_ID = b.id
)
SELECT ID,
       Parent_ID
FROM   b;

edited Nov 23 '17 at 09:58

MT0

143,790
11
59
117

answered Nov 23 '17 at 09:48

Thangamani Eraniyan

83
3

You are not concatenating the IDs - you need something like `WITH b( ID, path ) AS ( SELECT ID, NAME FROM DataTable WHERE Parent_ID IS NULL UNION ALL SELECT a.ID, b.PATH || a.NAME FROM DataTable a INNER JOIN b ON a.Parent_ID = b.ID ) ...` – MT0 Nov 23 '17 at 10:02

Oracle sql recursive

2 Answers2