--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[name] nvarchar(21),[parentId] int)
Insert #T
select 1,N'A',0 union all
select 2,N'B',1 union all
select 3,N'C',2 union all
select 4,N'D',3
Go
--测试数据结束
;WITH cte AS(
Select * from #T WHERE name='B' --这里的B，根据需要传不同的值
UNION ALL
SELECT #T.* FROM #T JOIN cte ON #T.parentId=cte.id
)
SELECT * FROM cte
