By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table category(nodeid number(3), parentid number(3), description varchar2(100) );
insert into category values(1,0, 'root description');
1 rows affected
insert into category values(2,1,'sub category description');
1 rows affected
insert into category(nodeid,parentid) values(3,2);
1 rows affected
insert into category(nodeid,parentid) values(4,3);
1 rows affected
insert into category values(5,4,'last description');
1 rows affected
select * from category;
| NODEID | PARENTID | DESCRIPTION |
|---|---|---|
| 1 | 0 | root description |
| 2 | 1 | sub category description |
| 3 | 2 | null |
| 4 | 3 | null |
| 5 | 4 | last description |
SELECT *
FROM (SELECT description
FROM category
START WITH parentid = 0
CONNECT BY PRIOR nodeid = parentid)
WHERE description IS NOT NULL;
| DESCRIPTION |
|---|
| root description |
| sub category description |
| last description |