sql 数据循环好难写，哪位高手来帮忙啊

create table tb1(a varchar(10),b,varchar(10))insert into tb1 select 'A01',    '001'insert into tb1 select 'A01',    '002'insert into tb1 select '001',    '0001'insert into tb1 select '001',    '0002'insert into tb1 select '001',    '0003'insert into tb1 select '0002',    '00005'insert into tb1 select 'A08',    '001'insert into tb1 select 'A08',    '002'select * from tb1上表是一个工程bom的表结构比如 我现在需要查A01的表结构就查询A01下面有几个料号，在查子料号下面又有几个料号，一直搜索到下面没有料号为止最后得到下面的数据，若第一列有重复，则为nullA01    001    002001    0001    0002    00030002    00005    A08    001    002001    0001    0002    00030002    00005

--测试数据DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))INSERT @t SELECT '001',NULL ,'山东省'UNION ALL SELECT '002','001','烟台市'UNION ALL SELECT '004','002','招远市'UNION ALL SELECT '003','001','青岛市'UNION ALL SELECT '005',NULL ,'四会市'UNION ALL SELECT '006','005','清远市'UNION ALL SELECT '007','006','小分市'--深度排序显示处理--生成每个节点的编码累计(相同当单编号法的编码)DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))DECLARE @Level intSET @Level=0INSERT @t_Level SELECT ID,@Level,IDFROM @tWHERE PID IS NULLWHILE @@ROWCOUNT>0BEGIN    SET @[email protected]+1    INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID    FROM @t a,@t_Level b    WHERE a.PID=b.ID        AND [email protected]END--显示结果SELECT SPACE(b.Level*2)+'|--'+a.NameFROM @t a,@t_Level bWHERE a.ID=b.IDORDER BY b.Sort/*--结果|--山东省  |--烟台市    |--招远市  |--青岛市|--四会市  |--清远市    |--小分市--*/
------解决方案--------------------
SQL code
create table tb1(a varchar(10),b varchar(10))insert into tb1 select 'A01',    '001'insert into tb1 select 'A01',    '002'insert into tb1 select '001',    '0001'insert into tb1 select '001',    '0002'insert into tb1 select '001',    '0003'insert into tb1 select '0002',    '00005'insert into tb1 select 'A08',    '001'insert into tb1 select 'A08',    '002'go;with cte as(    select *,cast(a as varchar(max)) as lev from tb1 t where not exists (select 1 from tb1 where b=t.a)    union all    select a.*,b.lev + '01'    from tb1 a join cte b on a.a = b.b),ach as(    select *,px=row_number() over (partition by a,lev order by b)    from cte)select (case when px=1 then a else '' end) a,bfrom achorder by lev,pxdrop table tb1/**************************a          b---------- ----------A01        001           002001        0001           0002           00030002       00005A08        001           002001        0001           0002           00030002       00005(12 行受影响)