SQL & PL/SQL

Hi,

So you want a different column for every level, is that right?
In Oracle 9, you can get all the data together into a delimited list using SYS_CONNECT_BY_PATH.
In Oracle 10, you cn use REGEXP_SUBSTR to pick out just the n-th item from that list. (In Oracle 9 you have to use INSTR and SUBSTR, which is more tedious.)

You didn't post CREATE TABLE and INSERT statements for your data, so I'll use the scott.emp table to illustrate:

WITH	got_path	AS
(
	SELECT	SYS_CONNECT_BY_PATH (ename, ',')	AS path
	,	ROWNUM		    	    		AS r_num
	FROM	scott.emp
	START WITH	mgr	IS NULL
	CONNECT BY	mgr	= PRIOR empno
)
SELECT	  REGEXP_SUBSTR (path, '[^,]+', 1, 1)	AS parent
,	  REGEXP_SUBSTR (path, '[^,]+', 1, 2)	AS child_1
,	  REGEXP_SUBSTR (path, '[^,]+', 1, 3)	AS child_2
FROM	  got_path
ORDER BY  r_num
;

Output:

PARENT     CHILD_1    CHILD_2
---------- ---------- ----------
KING
KING       JONES
KING       JONES      SCOTT
KING       JONES      SCOTT
KING       JONES      FORD
KING       JONES      FORD
KING       BLAKE
KING       BLAKE      ALLEN
KING       BLAKE      WARD
KING       BLAKE      MARTIN
KING       BLAKE      TURNER
KING       BLAKE      JAMES
KING       CLARK
KING       CLARK      MILLER

I delibnerately left out the child_3 column to show what happens when you have more levels than columns: the last columns simply don't appear. (SCOTT and FORD, above, have one child each.)
Of course, you can have as many columns as you like, but they have to be hard-coded into the query (or you have to use dynamic SQL to do that for you).

Things would be a lot simpler if you didn't need separate columns. You could use just SYS_CONNECT_BY_PATH to form one big string, formatted so that it looked like separate columns.

I like recursive with clause B-)

with rec(empno,mgr,PARENT,CHILD_1,CHILD_2,LV) as(
select empno,mgr,ename,null,null,1
FROM scott.emp
where mgr   IS NULL
union all
select b.empno,b.mgr,a.PARENT,
case LV+1 when 2 then b.ename else a.CHILD_1 end,
case LV+1 when 3 then b.ename else a.CHILD_2 end,a.LV+1
  from rec a,scott.emp b
 where a.empno = b.mgr)
search depth first by empno set SortKey
select * from rec order by SortKey;

EMPNO   MGR  PARENT  CHILD_1  CHILD_2  LV  SORTKEY
-----  ----  ------  -------  -------  --  -------
 7839  null  KING    null     null      1        1
 7566  7839  KING    JONES    null      2        2
 7788  7566  KING    JONES    SCOTT     3        3
 7876  7788  KING    JONES    SCOTT     4        4
 7902  7566  KING    JONES    FORD      3        5
 7369  7902  KING    JONES    FORD      4        6
 7698  7839  KING    BLAKE    null      2        7
 7499  7698  KING    BLAKE    ALLEN     3        8
 7521  7698  KING    BLAKE    WARD      3        9
 7654  7698  KING    BLAKE    MARTIN    3       10
 7844  7698  KING    BLAKE    TURNER    3       11
 7900  7698  KING    BLAKE    JAMES     3       12
 7782  7839  KING    CLARK    null      2       13
 7934  7782  KING    CLARK    MILLER    3       14