Oracle hierarchical path as separate row

Question

I have a table like below:

ID     PARENT_ID
---    ----------  
  1     null
  2     1
  3     2
  4     2
  5     4
  6     4
  7     1
  8     7
  9     1
 10     9
 11     10
 12     9
 13     null
 14     13                 

and I want query for get result like this:

  ID | PARENT_ID
-----+-----------
   1 |   1
   2 |   1
   2 |   2
   3 |   1
   3 |   2
   3 |   3
   4 |   1
   4 |   2
   4 |   4
   5 |   1
   5 |   2
   5 |   4
   5 |   5
   ...     

I used Oracle connected by root and get this result:

 ID PATH
--- -----------
 1  1
 2  1-2
 3  1-2-3
 4  1-2-4
 5  1-2-4-5
 ....

but it's not that I want.

Is there another way to get result without connected by root (prefer in standard SQL), that get same result from table?

Can anyone help me?

Note: I use an Oracle database

Thanks

Answer 1

The first step is to make a query that will produce for you all the parents above a specific node.

Here is a example of such a query:

    select * from
(SELECT parent_id
FROM test
START WITH ID = 4
CONNECT BY ID = PRIOR PARENT_ID) temp
where parent_id is not null

UNION

select ID from test where ID = 4 ;

In the above case, we start with Node 4.

The next step is to use this query, with another query to get the results for all nodes.

(Will produce this soon)

final solution

create table test(
  id int,
  parent_id int
);

insert into test values (1, null);
insert into test values (2,1);
insert into test values (3,2);
insert into test values (4,2);

select distinct ID, parent_id from
  (
SELECT a.parent_id as aParent, b.parent_id as bParent, b.id as ID, a.id as parent_id
FROM test a, test b
START WITH a.ID = b.id
CONNECT BY a.ID = PRIOR a.PARENT_ID
  ) temp
  where not (aParent is not null AND bParent is null)
order by id, parent_id;

Optimized

  SELECT distinct b.id as ID, a.id as parent_id
    FROM test a, test b
     where not (a.parent_id is not null and b.parent_id is null )
    START WITH a.ID = b.id
    CONNECT BY a.ID = PRIOR a.PARENT_ID order by id, parent_id;;

Boolean Algebra Simplification

   SELECT distinct
  findNodesAboveMe.id as ID,
  pathFollowing.id    as parent_id
FROM
  test pathFollowing,
  test findNodesAboveMe
where
  pathFollowing.parent_id is null
  OR findNodesAboveMe.parent_id is not null START WITH pathFollowing.ID = findNodesAboveMe.id CONNECT BY pathFollowing.ID = PRIOR pathFollowing.PARENT_ID
order by
  id,
  parent_id;

Fix for Null parents

select id, parent_id from
  (
    (SELECT DISTINCT
       findNodesAboveMe.id              AS ID,
       CASE WHEN pathFollowing.parent_id IS NULL
         THEN pathFollowing.id
       ELSE pathFollowing.parent_id END AS parent_id
     FROM
       test pathFollowing,
       test findNodesAboveMe
     WHERE
       findNodesAboveMe.parent_id IS NOT NULL
     START WITH pathFollowing.ID = findNodesAboveMe.id CONNECT BY pathFollowing.ID = PRIOR pathFollowing.PARENT_ID
    )
    UNION
    SELECT
      id,
      id AS parent_id
    FROM test
  ) order by id, parent_id
;

Update

select
  distinct bid as ID, aid as parent_id

  from
    (
      SELECT DISTINCT
        a.id as aid,
        a.parent_id as aparentid,
        b.id as bid,
        b.parent_id as bparentid,

        ltrim(sys_connect_by_path(a.id, ','), ',') AS pth
      FROM test a, test b
      WHERE NOT
            (a.parent_id IS NOT NULL AND b.parent_id IS NULL)
      START WITH a.ID = b.id
      CONNECT BY a.ID = PRIOR a.PARENT_ID
    ) temp
  where ( pth like bid or  pth like bid || ','|| bparentid || '%' )
 order by ID, parent_id;