create table ta(id varchar(20),[name] varchar(10))
insert ta select
'001','国内' union select 
'002','国外' union select 
'001001','江苏' union select 
'001002','浙江' union select 
'001003','广东' union select 
'001001001','南京' union select 
'001001002','无锡' union select 
'001001003','苏州' union select 
'001002001','杭州' union select 
'001002002','温州'
go
select isnull(a.name,'') +
       case when b.name is null then '' else  '->' + b.name  end+ 
       case when c.name is null then '' else  '->' + c.name end
from 
(select id,id as pid,name from ta where len(id ) = 3) a
left join
(select id,left(id,3) as pid,name from ta where len(id) = 6) b on a.pid = b.pid
left join
(select id,left(id,3) as pid ,left(id,6) as ppid,name from ta where len(id) = 9) c
on c.ppid = b.id
drop table ta


 
/*
---------------------------------- 
国内->江苏->南京
国内->江苏->无锡
国内->江苏->苏州
国内->浙江->杭州
国内->浙江->温州
国内->广东
国外

（所影响的行数为 7 行）
*/