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