Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Getting complete hierarchy (tree) of each row using single Oracle SQL statement

BilalSep 21 2020 — edited Sep 21 2020

Hi All,

Oracle Database 18c.

I have hierarchical data in a table using the conventional recursive FK constraint like generated by the following SQL query:

WITH hier (child_id, parent_id) AS

(SELECT 1, null from dual union all

SELECT 2, 1 from dual union all

SELECT 3, 1 from dual union all

SELECT 4, 2 from dual union all

SELECT 5, 2 from dual union all

SELECT 6, 5 from dual)

SELECT * FROM hier;

The query output:

Screen Shot 2020-09-21 at 10.36.26.png

I want to produce the output as sys_connect_by_path does, but in a row-centric way as shown below:

Screen Shot 2020-09-21 at 10.43.19.png

So every node_id in the output has complete hierarch in hier_node_id column as set of rows.

Any idea on how to do this using single SQL statement?

Your help will be very much appreciated.

Many Thanks and

Kind Regards,

Bilal
This post has been answered by mathguy on Sep 21 2020
Jump to Answer
Comments
Post Details
Added on Sep 21 2020
7 comments
2,928 views