create function fun(@child varchar(20))

returns varchar(20)

as

begin

declare @s varchar(20)

select @s= ' '

while exists(select 1 from test where id=@child and parentid <> 0)

begin

select @child=parentid from test where id=@child

select @s=@s+ '- '+cName from test where @child=parentid

end

select @s=cName+@s from test where id=@child

return @s

end

go

create table test(id int,parentid int,cName varchar(10))

insert test select 1,0, 'A '

union all select 2,0, 'B '

union all select 3,1, 'C '

union all select 4,3, 'D '

go

select id,dbo.fun(id) from test

order by id desc

drop table test

drop function fun

id

----------- --------------------

4 A-D-C

3 A-C

2 B

1 A