declare @test table(Rownum int identity(1,1), Level int, Parent nvarchar(10), ParentDes nvarchar(100), Child nvarchar(10))

insert into @test
select 1,'A','A description','a1' union all
select 2,'a1','a1 description','a2' union all
select 3,'a2','a2 description','a3' union all
select 1,'A','A description','b1' union all
select 2,'b1','b1 description','b2' union all
select 3,'b2','b2 description','b3' union all
select 1,'C','C description','c1' union all
select 2,'c1','c1 description','c2' union all
select 3,'c2','c2 description','c3' union all
select 1,'C','C description','d1' union all
select 2,'d1','d1 description','d2' union all
select 3,'d2','d2 description','d3' 

Rownum    Level    Parent    ParentDes    Child
1    1    A    A description    a1
2    2    a1    a1 description    a2
3    3    a2    a2 description    a3
4    1    A    A description    b1
5    2    b1    b1 description    b2
6    3    b2    b2 description    b3
7    1    C    C description    c1
8    2    c1    c1 description    c2
9    3    c2    c2 description    c3
10    1    C    C description    d1
11    2    d1    d1 description    d2
12    3    d2    d2 description    d3

上述结果1到6行都是父A展开的,7到12是父C展开的,现在想把每个父项展开的所有BOM的父和父描述全部替换成最上层的数据
结果如下:
Rownum    Level    Parent    ParentDes    Child
1    1    A    A description    a1
2    2    A    A description    a2
3    3    A    A description    a3
4    1    A    A description    b1
5    2    A    A description    b2
6    3    A    A description    b3
7    1    C    C description    c1
8    2    C    C description    c2
9    3    C    C description    c3
10    1    C    C description    d1
11    2    C    C description    d2
12    3    C    C description    d3

或者直接加上两列也行:
Rownum    Level    Parent    ParentDes    Child      Parent    ParentDes    
1    1    A    A description    a1        A            A description        
2    2    a1    a1 description    a2        A            A description  
3    3    a2    a2 description    a3        A            A description  
4    1    A    A description    b1        A            A description  
5    2    b1    b1 description    b2        A            A description  
6    3    b2    b2 description    b3        A            A description  
7    1    C    C description    c1         C            C description
8    2    c1    c1 description    c2         C            C description
9    3    c2    c2 description    c3         C            C description
10    1    C    C description    d1         C            C description
11    2    d1    d1 description    d2         C            C description
12    3    d2    d2 description    d3         C            C description

没有头绪呀

create function Getlevel(@child (nvarchar(100)))
returns nvarchar(100)
as 
begin
;with cte tb
(select * from table where child=@child  
union all
 select * from table s  inner join  tb on  s.child=tb.parent)
select @nm=[level] from tb where level=1
return @nm
end

-------------
select * ,level=Getlevel（child）from [table]