create table Hier
(
LocationID int,
 Name varchar(100),
  ParentID  int
  )

insert into Hier
select  1, 'Asia', Null union all
select  2 ,'Europe', Null union all
select  3 ,'China', 1 union all
select  4 ,'Beijing', 3  union all
select  5 ,'Azerbaijan', 2  union all
select  6 ,'Germany', 2 union all
select  7 ,'Munich', 6

;with cte(id, value,parentid,parentvalue,[path],[level]) as
(
select LocationID,Name,cast(null as int),cast(NULL as varchar(100)),cast(Name as varchar(max)),cast(1 as int)
FROM Hier
where ParentID is null
union all
select t.LocationID,t.Name,t.ParentID,c.value,cast(c.[path] + '/' + t.Name as varchar(max)),c.Level + 1
from CTE c
join Hier t
ON t.ParentID = c.id
)

select [1] as Continent,[2] As country, [3] as city
from(
select left(path + '/',charindex('/',path+'/')-1) as path, level,value
from CTE
)t
pivot (max(value) for [level] in ([1],[2],[3]))p


output
----------------------------------------
Continent	country	    city
-----------------------------------------
Asia	        China	    Beijing
Europe	        Germany	    Munich