You could use a recursive scalar function:-
set nocount on
create table location (
id int,
name varchar(50),
parent int
)
insert into location values
(1,'france',null),
(2,'paris',1),
(3,'belleville',2),
(4,'lyon',1),
(5,'vaise',4),
(6,'united kingdom',null),
(7,'england',6),
(8,'manchester',7),
(9,'fallowfield',8),
(10,'withington',8)
go
create function dbo.breadcrumb(@child int)
returns varchar(1024)
as begin
declare @returnValue varchar(1024)=''
declare @parent int
select @returnValue+=' > '+name,@parent=parent
from location
where id=@child
if @parent is not null
set @returnValue=dbo.breadcrumb(@parent)+@returnValue
return @returnValue
end
go
declare @location int=1
while @location<=10 begin
print dbo.breadcrumb(@location)+' >'
set @location+=1
end
produces:-
> france >
> france > paris >
> france > paris > belleville >
> france > lyon >
> france > lyon > vaise >
> united kingdom >
> united kingdom > england >
> united kingdom > england > manchester >
> united kingdom > england > manchester > fallowfield >
> united kingdom > england > manchester > withington >