Best way in Oracle to go from this data to this result

Question

What is the best way in oracle to ge the following result set from a table like this:

GROUP ID VALUE
--------------------
1       1     A
1       2     B
1       3     C
2       4     D
2       5     E
3       6     F
4       7     G
4       8     H


ID  Parent  VALUE
---------------------
1       0     A
2       1     B
3       2     C
4       0     D
5       4     E
6       0     F
7       0     G
8       7     H

Essentially what this is doing is taking data that is grouped by a certain number and making a parent child relationship out of the entries in the group.

The point of doing this is to add it to a connect by query to return the values in one column separated by commas.

Now I can do it with a bunch of sub queries and lag functions, but it seems like there would be a quicker way to do it.

Or..am I totally missing something and can directly use the connect by prior command on the original data. That would be optimum!

EDIT

It seems like I may have confused the situation. My ultimate goal (but not the question here) is to query group number 1 and get the result A,B,C as a string in one single column.

I know how to do that with the Connect by clause. That is not the issue.

The issue is that in order to use the connect by clause you have to actually have the data in a hierarchical format.

That is what I'm looking for a more efficient way to do.

Answer 1

Is there any problem that prevents you from doing:

SELECT t.ID,
       NVL((SELECT MAX(t2.ID)
              FROM Table t2
             WHERE t.GROUP = t2.GROUP
               AND t2.ID < t.ID), 0)      AS Parent_ID ,
       t.Value
  FROM TABLE t

May be you prefered a Connect By Query but if the performance is not too bad I feel its quite simple. If the performance is important for you may be able to try something like.

WITH aux AS( SELECT ID,
                     Value,
                     Group,
                     dense_rack() ( partition by GROUP order by ID) AS num
                FROM TABLE )

         SELECT t1.ID,
                t2.ID  AS parent,
                t1.Value
           FROM ( select * from aux ) t1  
      LEFT JOIN ( select * from aux ) t2 
             ON t1.group = t2.group 
            AND t1.ID    = t2.ID -1

Answer 2

After digesting borjab's answer, I found another quick way to do it with the lag function:

SELECT t.ID,
       LAG(ID,1,0) OVER (PARTITION BY GROUP ORDER BY ID) AS Parent_ID ,
       t.Value
FROM TABLE t