The obvious issue with this sort of query is that you have to have a finite limit to the depth of recursion that can be displayed.

You may be better of , making this a display issue and doing the work in the presentation layer.

In any case , here's how you can handle this in SQL

Create table PrtChild

(

Id integer not null,

PrtId integer null,

Name varchar(20)

)

go

insert into PrtChild values(1,null,'Dave')

insert into PrtChild values(2,1,'Tom')

insert into PrtChild values(3,1,'Dick')

insert into PrtChild values(4,2,'Harry')

go

with cteTree

as

(

Select Id,PrtId,

Name as Path1,

cast(Null as varchar(255)) as Path2,

cast(Null as varchar(255)) as Path3,

cast(Null as varchar(255)) as Path4,

cast(Null as varchar(255)) as Path5,

0 as Level

from PrtChild

where PrtId is null

union all

Select Child.Id,

Child.PrtID,

Path1,

case when Level+1 = 1 then Name else Path2 end,

case when Level+1 = 2 then Name else Path3 end,

case when Level+1 = 3 then Name else Path4 end,

case when Level+1 = 4 then Name else Path5 end,

Level+1

from CteTree

join PrtChild child

on child.PrtId = CteTree.Id

)

select * from cteTree