Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> How to LPADding parents' names in a connect by query?
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 0I'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