跪求高手帮忙用SQL语句展BOM
1、表结构如下
PartNum Mtlseq MtlPartNum Quantity Rev
17138090001004 10 21138090201002 1 A
17138090001004 20 63302490175056 0.1 A
21138090201002 10 62210560460008 0.2 A
21138090201002 20 5011414 0.3 A
62210560460008 10 5011414 0.2 A
5011414 10 51200 1 A
2、目前我写的存储过程只能展成如下样式
PartNum Mtlseq PMtlSeq ArtIndex Level MtlPartNum Rev Quantity Levels
17138090001004 10 10 10 *1 21138090201002 A 1 1
17138090001004 20 20 20 *1 63302490175056 A 0.1 1
21138090201002 10 10.10 10.10 *2 62210560460008 A 0.2 2
21138090201002 20 10.20 10.20 *2 5011414 A 0.3 2
62210560460008 10 10.10.10 10.10.10 *3 5011414 A 0.2 3
5011414 10 10.20.10 10.20.10 *3 51200 A 1 3
5011414 10 10.10.10.10 10.20.10.10 *4 51200 A 1 4
3、我希望得到的结果
PartNum Mtlseq PMtlSeq ArtIndex Level MtlPartNum Rev Quantity Levels
17138090001004 10 10 10 *1 21138090201002 A 1 1
21138090201002 20 10.20 10.20 *2 5011414 A 0.3 2
5011414 10 10.20.10 10.20.10 *3 51200 A 1 3
21138090201002 10 10.10 10.10 *2 62210560460008 A 0.2 2
62210560460008 10 10.10.10 10.10.10 *3 5011414 A 0.2 3
5011414 10 10.10.10.10 10.20.10.10 *4 51200 A 1 4
17138090001004 20 20 20 *1 63302490175056 A 0.1 1
存储过程如下
ALTER PROCEDURE [dbo].[RDBOMLiset]
as
CREATE Table #BomTree
(
PartNum nvarchar(50) NULL,--主件編號
MtlSeq nvarchar(4) NULL,--序號
PMtlSeq nvarchar(100) NULL,--配件序號(含上層序號,形如0010.0020.0070)
ArtIndex nvarchar(100) NULL,--配件項號(含上層項號,形如1.2.7)
[Level] nvarchar(50) NULL,--配件層(形如***3)
MtlPartNum nvarchar(50) NULL,--配件編號
Rev nvarchar(4) NULL,--版本號
Quantity NUMERIC(11,3) NULL,--生產實際用量
Levels int NULL--層數
)
---讀取簡單BOM結構暫存臨時表#BomTree中
DECLARE @Level int
declare @PartNum nvarchar(50)
SET @Level=1
set @PartNum='17138090001004'
insert into #BomTree select @PartNum,0,NULL,NULL,NULL,@PartNum,Null,1,1
while @@ROWCOUNT>0
Begin
set @Level=@Level+1
if @Level=2
Begin
insert into #BomTree
SELECT A.PartNum,A.MtlSeq,CAST(A.MtlSeq AS nvarchar(100)), CAST(A.MtlSeq AS nvarchar(100)),REPLICATE('*',@Level-1)+CAST(@Level-1 AS VARCHAR(10)),
A.MtlPartNum,A.RevisionNum,A.QtyPer,@Level
FROM [epicor905].dbo.PartMtl A,#BomTree B
WHERE A.PartNum=B.MtlPartNum AND B.Levels=@Level-1
End
else
Begin
if @Level>2
Begin
insert into #BomTree
SELECT A.PartNum,A.MtlSeq,B.PMtlSeq+'.'+CAST(A.MtlSeq AS nvarchar(100)),B.ArtIndex+'.'+CAST(A.MtlSeq AS nvarchar(100)),REPLICATE('*',@Level-1)+CAST(@Level-1 AS VARCHAR(10)),
A.MtlPartNum,A.RevisionNum,A.QtyPer,@Level
FROM [epicor905].dbo.PartMtl A,#BomTree B
WHERE A.PartNum=B.MtlPartNum AND B.Levels=@Level-1
End
End
End
有哪位高手可以帮我下,要怎样写才能得到第3点我想要的样式,感激不尽