SQLServer 父子结构group汇总显示

需求：

SmCode	PdtCode	OrignBrand
A	A1	null
B	B1	A
C	C1	A
D	D1	C
E	E1	null
F	F1	null
G	G1	F

如上表格，A产生了B\C,C产生了D,所以A\B\C\D都属于一类，E单独，F\G一类

需要显示为：

SmCode	SmCodeList	PdtCodeList
A	A,B,C,D	A1,B1,C1,D1
E	E	E1
F	F,G	F1,G1

 

代码如下：

alter proc P_SampePdtList as 
begin
delete from ZTR_CodeList;

select s.SmCode,d.Code,s.OriginBrand into #temp from DB_Product d,SM_SampleClothing s where d.SampleId=s.Id

select * into #temp1 from (
select * from #temp union
select distinct OriginBrand,null,null from #temp where OriginBrand is not null and OriginBrand not in (select SmCode from SM_SampleClothing)
) t;

with CTEGetChild as
(
select *,SmCode oid from #temp1 where OriginBrand is null
UNION ALL
(SELECT a.*,oid from #temp1 as a inner join
CTEGetChild as b on a.OriginBrand=b.SmCode
)
)
--insert into ZTR_CodeList
SELECT oid,stuff
(
    (
        select ','+SmCode from CTEGetChild t where oid=CTEGetChild.oid for xml path('')
    ),
    1, 
    1, 
    ''
) as SmCodeList,stuff
(
    (
        select ','+Code from CTEGetChild t where oid=CTEGetChild.oid for xml path('')
    ),
    1, 
    1, 
    ''
) as PdtCodeList,getdate() CreateTime  FROM CTEGetChild group by oid

end