Hierarchy Display Sybase Table Data

EID PID Name 1 NULL A 2 1 B 3 2 C 4 3 D 5 1 E 6 1 F 7 1 G 8 6 H The above inf

Solution 1:

Because you know the number of levels, you can do this using left join:

select l1.name as level1, l2.name as level2,
       l3.name as level3, l4.name as level4
from data l1 left join
     data l2
     on l2.pid = l1.id left join
     data l3
     on l3.pid = l2.id left join
     data l4
     on l4.pid = l3.id;

Solution 2:

Disclaimer: it's a example made in (MS-SQL)

@Gordon Linoff answer almost did it, just forgot the base case (where Pid is null)

just follow this pattern of left joins + unions to cover the base cases for a fixed amount of levels, for a dynamic number os levels you ill need to use recursion

declare @Tree as
table (
    Eid int not null
   ,Pid int null
   ,Name char(1) not null
)

insert into @Tree
values
 (1, NULL, 'A')
,(2,    1, 'B')
,(3,    2, 'C')
,(4,    3, 'D')
,(5,    1, 'E')
,(6,    1, 'F')
,(7,    1, 'G')
,(8,    6, 'H')

(
select t1.Name as [Level 1], null as [Level 2], null as [Level 3], null as [Level 4]
from @Tree t1
where t1.Pid is null
union
select t1.Name as [Level 1], t2.Name as [Level 2], null as [Level 3], null as [Level 4]
from @Tree t1
left join @Tree t2 on t2.Pid = t1.Eid
where t1.Pid is null
union
select t1.Name as [Level 1], t2.Name as [Level 2], t3.Name as [Level 3], null as [Level 4]
from @Tree t1
left join @Tree t2 on t2.Pid = t1.Eid
left join @Tree t3 on t3.Pid = t2.Eid
where t1.Pid is null
union
select t1.Name as [Level 1], t2.Name as [Level 2], t3.Name as [Level 3], t4.Name as [Level 4]
from @Tree t1
left join @Tree t2 on t2.Pid = t1.Eid
left join @Tree t3 on t3.Pid = t2.Eid
left join @Tree t4 on t4.Pid = t3.Eid
where t1.Pid is null
) order by [Level 1], [Level 2], [Level 3], [Level 4]

Edit: and the same query using right join instead of left joins

declare @Tree as
table (
    Eid int not null
   ,Pid int null
   ,Name char(1) not null
)

insert into @Tree
values
 (1, NULL, 'A')
,(2,    1, 'B')
,(3,    2, 'C')
,(4,    3, 'D')
,(5,    1, 'E')
,(6,    1, 'F')
,(7,    1, 'G')
,(8,    6, 'H')

(
select t1.Name as [Level 1], null as [Level 2], null as [Level 3], null as [Level 4]
from @Tree t1
where t1.Pid is null
union
select t1.Name as [Level 1], t2.Name as [Level 2], null as [Level 3], null as [Level 4]
from @Tree t2
right join @Tree t1 on t2.Pid = t1.Eid
where t1.Pid is null
union
select t1.Name as [Level 1], t2.Name as [Level 2], t3.Name as [Level 3], null as [Level 4]
from @Tree t3
right join @Tree t2 on t3.Pid = t2.Eid
right join @Tree t1 on t2.Pid = t1.Eid
where t1.Pid is null
union
select t1.Name as [Level 1], t2.Name as [Level 2], t3.Name as [Level 3], t4.Name as [Level 4]
from @Tree t4
right join @Tree t3 on t4.Pid = t3.Eid
right join @Tree t2 on t3.Pid = t2.Eid
right join @Tree t1 on t2.Pid = t1.Eid
where t1.Pid is null
) order by [Level 1], [Level 2], [Level 3], [Level 4]