How to LPADding parents' names in a connect by query?

From: <yong321_at_yahoo.com>
Date: Wed, 05 Apr 2000 17:07:07 GMT
Message-ID: <8cfrrd$8gv$1@nnrp1.deja.com>

Please help me with the following:

create table test (id integer, name varchar2(10), parentid integer); insert into test(id, name) values (0, 'top');

insert into test values (1, 'mid1', 0);
insert into test values (2, 'mid2', 0);
insert into test values (3, 'bottom', 1);

col name for a20
select id, lpad(' ',4*(level-1))||name name, parentid from test
start with id = 0
connect by prior id = parentid;

The query result is:

       ID NAME                  PARENTID
--------- -------------------- ---------
        0 top
        1     mid1                     0
        3         bottom               1
        2     mid2                     0

I'd like to see the second row to be "top/mid1", third row "top/mid1/bottom" and last row "top/mid2". I.e., instead of lpadding with spaces, I want to pad with parents' (or parents' parents' and so on) names. Is this possible? I can't even get padding one-level parent to work, much less all parents. Thanks for help.

--
Yong Huang

(yong321_at_yahoo.com)
(http://www.stormloader.com/yonghuang/)

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Apr 05 2000 - 12:07:07 CDT