求SQL语句, 返回所有树状结构节点编号及节点名称

传入:000001001001001 传出101100001 成品传感器SSS
即当MA001='***'时，自动按3位进行自动生成序号功能。
传入:000001001001 传出101100 成品传感器SSS

谢谢！

[解决办法]

SELECT [id]
      ,[name]
      ,[nodeid]
      ,[fnodeid]
  FROM [mydb].[dbo].[TEST_TABLE]
--内容
idnamenodeidfnodeid
1成品000001000
2半成品000002000
3原材料000003000
01传感器000001001000001
100SSS000001001001000001001

--查询
with temp_table(id, name, nodeid, fnodeid, levell)
as
(
select CAST(ID AS VARCHAR) AS ID, CAST (NAME AS VARCHAR) AS NAME, nodeid, fnodeid, 1 AS LEVELL
from test_table WHERE NODEID = '000001001001'
union all
select CAST(T.ID + VT.ID AS VARCHAR) AS ID, cast (T.NAME + VT.NAME as varchar)  AS NAME, T.NODEID, T.FNODEID, VT.LEVELL + 1 AS LEVELL
FROM TEST_TABLE T, TEMP_TABLE VT
WHERE T.NODEID = VT.FNODEID
)
SELECT TOP 1 ID, name  FROM TEMP_TABLE ORDER BY LEVELL DESC
--内容
IDname
101100成品传感器SSS