Hi there, I would like to accomplish the following, I have a table as follows:

cat_id
parent_id
cat_descr


------------------
Table filled as:
1, 0, Root
2, 1, Computer
3, 2, Harddisk
4, 2, Floppy drive

I would like to get a display as of:

Root
Root > Computer
Root > Computer > Harddisk
Root > Computer > Floppy drive

Thanks!

yes it's possible

you need three queries in a union

In general you have to use recursion (Oracle "connect by", DB2/MS SQL Server Yukon - "with")

DB2 example (I added a limit by level, remove if not needed):

with
yourtable(cat_id, parent_id, cat_descr) as (values (1, 0, 'Root'), (2, 1, 'Computer'), (3, 2, 'Harddisk'), (4, 2, 'Floppy drive'))
, tree(level, id, value) as
(
select 1, cat_id, cast(cat_descr as varchar(100)) from yourtable where parent_id = 0
union all
select t2.level+1, t1.cat_id, t2.value || '>' || t1.cat_descr
from
yourtable t1, tree t2
where t1.parent_id = t2.id
and t2.level < 10
)
select value
from tree
order by level



OUTPUT:

VALUE
---------------------------
Root
Root>Computer
Root>Computer>Harddisk
Root>Computer>Floppy drive