create table tb(typeid int,parentid int,name varchar(10))
insert into tb select 1, 0, 't1'
insert into tb select 2, 0, 't2'
insert into tb select 3, 1, 'm'
insert into tb select 4, 2, 'n'
insert into tb select 5, 3, 'a'
go
;with cte as(
select b.typeid as id,a.name as name1,b.name as name2 from tb a inner join tb b on a.typeid=b.parentid
)select a.id,
(case when b.name1 is null then a.name1 else b.name1 end) name1,
(case when b.name1 is null then a.name2 else b.name2 end) name2,
(case when b.name1 is null then '' else a.name2 end) name3
from cte a left join cte b on a.name1=b.name2
/*
id          name1      name2      name3
----------- ---------- ---------- ----------
3           t1         m          
4           t2         n          
5           t1         m          a

(3 行受影响)

*/
go
drop table tb