Insert into #Tmp_iBomDetTbl Values ('A','',100,100)
Insert into #Tmp_iBomDetTbl Values ('B1','A',100,100)
Insert into #Tmp_iBomDetTbl Values ('B2','A',100,100)
Insert into #Tmp_iBomDetTbl Values ('C1','B1',100,100)
Insert into #Tmp_iBomDetTbl Values ('C2','B1',100,100)
Insert into #Tmp_iBomDetTbl Values ('C3','B2',100,100)
Insert into #Tmp_iBomDetTbl Values ('C4','B2',100,100)
--Select * From #Tmp_iBomDetTbl
if object_id('tempdb..#Tmp_ExpandBom') is not null
drop table #Tmp_ExpandBom
--先加入最上層的一條記錄,
INSERT #Tmp_ExpandBom (Level_No,Level_Desc,Item_No,Cost1,Cost2,xPath)
Select Distinct @Level_No,dbo.IntToStr(@Level_No,3),a.ParentItem_No,Cost1,Cost2,ParentItem_No
From #Tmp_iBomDetTbl a
Where Not Exists(select SubItem_No from #Tmp_iBomDetTbl where SubItem_No=a.ParentItem_No )
WHILE @@rowcount>0 --根據最上層的一條記錄依次找出下一級數據
BEGIN
SET @Level_No=@Level_No+1
INSERT into #Tmp_ExpandBom (Level_No,Level_Desc,Item_No,Cost1,cost2,xPath)
Select Level_No,Level_Desc,SubItem_No,Cost1,cost2,xPath
From
(
Select
@Level_No as 'Level_No',
b.Level_Desc+dbo.IntToStr((1+(Select count(ParentItem_No)
From #Tmp_iBomDetTbl x join #Tmp_ExpandBom y on x.ParentItem_No=y.Item_No
Where y.Level_No=@Level_No-1
and x.Detail_id<a.Detail_id
and x.ParentItem_No=a.ParentItem_No)),3) as 'Level_Desc',
a.SubItem_No,
a.Cost1,a.Cost2,
b.xPath+'>'+a.SubItem_No as 'xPath'
From #Tmp_iBomDetTbl a join #Tmp_ExpandBom b on a.ParentItem_No=b.Item_No
Where b.Level_No=@Level_No-1
) a
End
Select xPath,Cost1,Cost2 From #Tmp_ExpandBom
#3
层级是不确定的,完全根据数据生成。
#4
dbo.IntToStr() 函数提供一下?
#5
Create FUNCTION [dbo].[IntToStr]
(@IntValue int,@Lenth int)
RETURNS Nvarchar(20)
AS
BEGIN
Return REPLICATE('0',@Lenth-len(ltrim(rtrim(str( @IntValue))))) + ltrim(rtrim(str(@IntValue)))
END
WITH CTE1
AS
(SELECT *,ParentItem_No AS NO1,SubItem_No AS NO2,CAST('' AS VARCHAR(50)) AS NO3 ,1 AS LEVEL
FROM #A A
WHERE EXISTS (SELECT 1 FROM #A WHERE A.SubItem_No=ParentItem_No)
AND EXISTS (SELECT 1 FROM #A WHERE A.ParentItem_No=SubItem_No)
UNION ALL
SELECT B.*,B.SubItem_No,CAST('' AS VARCHAR(50)),CAST('' AS VARCHAR(50)),C.LEVEL+1
FROM #A B
JOIN CTE1 C ON B.SubItem_No=C.ParentItem_No
WHERE C.LEVEL>=1
UNION ALL
SELECT D.*,E.NO1,E.NO2,CAST(D.SubItem_No AS VARCHAR(50)),E.LEVEL-1
FROM #A D
JOIN CTE1 E ON D.ParentItem_No=E.SubItem_No
WHERE E.LEVEL<=1)
DECLARE @minLevel_No int
Select @minLevel_No=Min(Level_No) From #Tmp_ExpandBom Where Level_No>1
While @minLevel_No is not null
Begin
Select @Item_No=Item_No From #Tmp_ExpandBom Where Level_No=@minLevel_No
SET @Title='Item'+CAST(@minLevel_No-1 as varchar)
SET @sql=' Alter Table #Tmp_ExpandBom Add ' + @Title+' nvarchar(100) '
exec (@sql)
set @sql=''
SET @sql=@sql+' Update #Tmp_ExpandBom '
+' Set '+@Title+' ='''+@Item_No+''''
+' Where Level_No>'+cast(@minLevel_No-1 as varchar)
exec (@sql)
Select @minLevel_No=MIN(Level_No) From #Tmp_ExpandBom Where Level_No>@minLevel_No
End
Select * From #Tmp_ExpandBom
上一个有误,用这个吧
DECLARE @sql nvarchar(2000)
SET @sql=''
DECLARE @Title nvarchar(50)
DECLARE @Level_Desc varchar(100)
DECLARE @lenLevel_Desc int
DECLARE @Item_No varchar(50)
DECLARE @PrevLevel_No int
SET @PrevLevel_No=0
DECLARE @CurrLevel_No int
DECLARE @minDetail_ID int
Select @minDetail_ID=Min(Detail_ID) From #Tmp_ExpandBom Where Detail_ID>1
While @minDetail_ID is not null
Begin
Select @Level_Desc=Level_Desc,@lenLevel_Desc=len(level_desc),@Item_No=Item_No,@CurrLevel_No=Level_No
From #Tmp_ExpandBom
Where Detail_ID=@minDetail_ID
IF @CurrLevel_No>1 And @CurrLevel_No<>@PrevLevel_No
Begin
SET @PrevLevel_No=@CurrLevel_No
SET @Title='Item'+CAST(@CurrLevel_No-1 as varchar)
SET @sql=' Alter Table #Tmp_ExpandBom Add ' + @Title+' nvarchar(100) '
exec (@sql)
End
SET @sql=''
SET @sql=@sql+' Update #Tmp_ExpandBom '
+' Set '+@Title+' ='''+@Item_No+''''
+' Where substring(Level_Desc,1,'+cast(@lenLevel_Desc as varchar)+')='''+@Level_Desc+''''
exec (@sql)
Select @minDetail_ID=MIN(Detail_ID) From #Tmp_ExpandBom Where Detail_ID>@minDetail_ID
Insert into #Tmp_iBomDetTbl Values ('A','',100,100)
Insert into #Tmp_iBomDetTbl Values ('B1','A',100,100)
Insert into #Tmp_iBomDetTbl Values ('B2','A',100,100)
Insert into #Tmp_iBomDetTbl Values ('C1','B1',100,100)
Insert into #Tmp_iBomDetTbl Values ('C2','B1',100,100)
Insert into #Tmp_iBomDetTbl Values ('C3','B2',100,100)
Insert into #Tmp_iBomDetTbl Values ('C4','B2',100,100)
--Select * From #Tmp_iBomDetTbl
if object_id('tempdb..#Tmp_ExpandBom') is not null
drop table #Tmp_ExpandBom
--先加入最上層的一條記錄,
INSERT #Tmp_ExpandBom (Level_No,Level_Desc,Item_No,Cost1,Cost2,xPath)
Select Distinct @Level_No,dbo.IntToStr(@Level_No,3),a.ParentItem_No,Cost1,Cost2,ParentItem_No
From #Tmp_iBomDetTbl a
Where Not Exists(select SubItem_No from #Tmp_iBomDetTbl where SubItem_No=a.ParentItem_No )
WHILE @@rowcount>0 --根據最上層的一條記錄依次找出下一級數據
BEGIN
SET @Level_No=@Level_No+1
INSERT into #Tmp_ExpandBom (Level_No,Level_Desc,Item_No,Cost1,cost2,xPath)
Select Level_No,Level_Desc,SubItem_No,Cost1,cost2,xPath
From
(
Select
@Level_No as 'Level_No',
b.Level_Desc+dbo.IntToStr((1+(Select count(ParentItem_No)
From #Tmp_iBomDetTbl x join #Tmp_ExpandBom y on x.ParentItem_No=y.Item_No
Where y.Level_No=@Level_No-1
and x.Detail_id<a.Detail_id
and x.ParentItem_No=a.ParentItem_No)),3) as 'Level_Desc',
a.SubItem_No,
a.Cost1,a.Cost2,
b.xPath+'>'+a.SubItem_No as 'xPath'
From #Tmp_iBomDetTbl a join #Tmp_ExpandBom b on a.ParentItem_No=b.Item_No
Where b.Level_No=@Level_No-1
) a
End
Insert into #Tmp_iBomDetTbl Values ('A','',100,100)
Insert into #Tmp_iBomDetTbl Values ('B1','A',100,100)
Insert into #Tmp_iBomDetTbl Values ('B2','A',100,100)
Insert into #Tmp_iBomDetTbl Values ('C1','B1',100,100)
Insert into #Tmp_iBomDetTbl Values ('C2','B1',100,100)
Insert into #Tmp_iBomDetTbl Values ('C3','B2',100,100)
Insert into #Tmp_iBomDetTbl Values ('C4','B2',100,100)
--Select * From #Tmp_iBomDetTbl
if object_id('tempdb..#Tmp_ExpandBom') is not null
drop table #Tmp_ExpandBom
--先加入最上層的一條記錄,
INSERT #Tmp_ExpandBom (Level_No,Level_Desc,Item_No,Cost1,Cost2,xPath)
Select Distinct @Level_No,dbo.IntToStr(@Level_No,3),a.ParentItem_No,Cost1,Cost2,ParentItem_No
From #Tmp_iBomDetTbl a
Where Not Exists(select SubItem_No from #Tmp_iBomDetTbl where SubItem_No=a.ParentItem_No )
WHILE @@rowcount>0 --根據最上層的一條記錄依次找出下一級數據
BEGIN
SET @Level_No=@Level_No+1
INSERT into #Tmp_ExpandBom (Level_No,Level_Desc,Item_No,Cost1,cost2,xPath)
Select Level_No,Level_Desc,SubItem_No,Cost1,cost2,xPath
From
(
Select
@Level_No as 'Level_No',
b.Level_Desc+dbo.IntToStr((1+(Select count(ParentItem_No)
From #Tmp_iBomDetTbl x join #Tmp_ExpandBom y on x.ParentItem_No=y.Item_No
Where y.Level_No=@Level_No-1
and x.Detail_id<a.Detail_id
and x.ParentItem_No=a.ParentItem_No)),3) as 'Level_Desc',
a.SubItem_No,
a.Cost1,a.Cost2,
b.xPath+'>'+a.SubItem_No as 'xPath'
From #Tmp_iBomDetTbl a join #Tmp_ExpandBom b on a.ParentItem_No=b.Item_No
Where b.Level_No=@Level_No-1
) a
End
Select xPath,Cost1,Cost2 From #Tmp_ExpandBom
dbo.IntToStr() 函数提供一下?
#5
if object_id('tempdb..#Tmp_iBomDetTbl') is not null
drop table #Tmp_iBomDetTbl
Insert into #Tmp_iBomDetTbl Values ('A','',100,100)
Insert into #Tmp_iBomDetTbl Values ('B1','A',100,100)
Insert into #Tmp_iBomDetTbl Values ('B2','A',100,100)
Insert into #Tmp_iBomDetTbl Values ('C1','B1',100,100)
Insert into #Tmp_iBomDetTbl Values ('C2','B1',100,100)
Insert into #Tmp_iBomDetTbl Values ('C3','B2',100,100)
Insert into #Tmp_iBomDetTbl Values ('C4','B2',100,100)
--Select * From #Tmp_iBomDetTbl
if object_id('tempdb..#Tmp_ExpandBom') is not null
drop table #Tmp_ExpandBom
--先加入最上層的一條記錄,
INSERT #Tmp_ExpandBom (Level_No,Level_Desc,Item_No,Cost1,Cost2,xPath)
Select Distinct @Level_No,dbo.IntToStr(@Level_No,3),a.ParentItem_No,Cost1,Cost2,ParentItem_No
From #Tmp_iBomDetTbl a
Where Not Exists(select SubItem_No from #Tmp_iBomDetTbl where SubItem_No=a.ParentItem_No )
WHILE @@rowcount>0 --根據最上層的一條記錄依次找出下一級數據
BEGIN
SET @Level_No=@Level_No+1
INSERT into #Tmp_ExpandBom (Level_No,Level_Desc,Item_No,Cost1,cost2,xPath)
Select Level_No,Level_Desc,SubItem_No,Cost1,cost2,xPath
From
(
Select
@Level_No as 'Level_No',
b.Level_Desc+dbo.IntToStr((1+(Select count(ParentItem_No)
From #Tmp_iBomDetTbl x join #Tmp_ExpandBom y on x.ParentItem_No=y.Item_No
Where y.Level_No=@Level_No-1
and x.Detail_id<a.Detail_id
and x.ParentItem_No=a.ParentItem_No)),3) as 'Level_Desc',
a.SubItem_No,
a.Cost1,a.Cost2,
b.xPath+'>'+a.SubItem_No as 'xPath'
From #Tmp_iBomDetTbl a join #Tmp_ExpandBom b on a.ParentItem_No=b.Item_No
Where b.Level_No=@Level_No-1
) a
End
Select xPath,Cost1,Cost2 From #Tmp_ExpandBom
dbo.IntToStr() 函数提供一下?
Create FUNCTION [dbo].[IntToStr]
(@IntValue int,@Lenth int)
RETURNS Nvarchar(20)
AS
BEGIN
Return REPLICATE('0',@Lenth-len(ltrim(rtrim(str( @IntValue))))) + ltrim(rtrim(str(@IntValue)))
END
WITH CTE1
AS
(SELECT *,ParentItem_No AS NO1,SubItem_No AS NO2,CAST('' AS VARCHAR(50)) AS NO3 ,1 AS LEVEL
FROM #A A
WHERE EXISTS (SELECT 1 FROM #A WHERE A.SubItem_No=ParentItem_No)
AND EXISTS (SELECT 1 FROM #A WHERE A.ParentItem_No=SubItem_No)
UNION ALL
SELECT B.*,B.SubItem_No,CAST('' AS VARCHAR(50)),CAST('' AS VARCHAR(50)),C.LEVEL+1
FROM #A B
JOIN CTE1 C ON B.SubItem_No=C.ParentItem_No
WHERE C.LEVEL>=1
UNION ALL
SELECT D.*,E.NO1,E.NO2,CAST(D.SubItem_No AS VARCHAR(50)),E.LEVEL-1
FROM #A D
JOIN CTE1 E ON D.ParentItem_No=E.SubItem_No
WHERE E.LEVEL<=1)
DECLARE @minLevel_No int
Select @minLevel_No=Min(Level_No) From #Tmp_ExpandBom Where Level_No>1
While @minLevel_No is not null
Begin
Select @Item_No=Item_No From #Tmp_ExpandBom Where Level_No=@minLevel_No
SET @Title='Item'+CAST(@minLevel_No-1 as varchar)
SET @sql=' Alter Table #Tmp_ExpandBom Add ' + @Title+' nvarchar(100) '
exec (@sql)
set @sql=''
SET @sql=@sql+' Update #Tmp_ExpandBom '
+' Set '+@Title+' ='''+@Item_No+''''
+' Where Level_No>'+cast(@minLevel_No-1 as varchar)
exec (@sql)
Select @minLevel_No=MIN(Level_No) From #Tmp_ExpandBom Where Level_No>@minLevel_No
End
Select * From #Tmp_ExpandBom
上一个有误,用这个吧
DECLARE @sql nvarchar(2000)
SET @sql=''
DECLARE @Title nvarchar(50)
DECLARE @Level_Desc varchar(100)
DECLARE @lenLevel_Desc int
DECLARE @Item_No varchar(50)
DECLARE @PrevLevel_No int
SET @PrevLevel_No=0
DECLARE @CurrLevel_No int
DECLARE @minDetail_ID int
Select @minDetail_ID=Min(Detail_ID) From #Tmp_ExpandBom Where Detail_ID>1
While @minDetail_ID is not null
Begin
Select @Level_Desc=Level_Desc,@lenLevel_Desc=len(level_desc),@Item_No=Item_No,@CurrLevel_No=Level_No
From #Tmp_ExpandBom
Where Detail_ID=@minDetail_ID
IF @CurrLevel_No>1 And @CurrLevel_No<>@PrevLevel_No
Begin
SET @PrevLevel_No=@CurrLevel_No
SET @Title='Item'+CAST(@CurrLevel_No-1 as varchar)
SET @sql=' Alter Table #Tmp_ExpandBom Add ' + @Title+' nvarchar(100) '
exec (@sql)
End
SET @sql=''
SET @sql=@sql+' Update #Tmp_ExpandBom '
+' Set '+@Title+' ='''+@Item_No+''''
+' Where substring(Level_Desc,1,'+cast(@lenLevel_Desc as varchar)+')='''+@Level_Desc+''''
exec (@sql)
Select @minDetail_ID=MIN(Detail_ID) From #Tmp_ExpandBom Where Detail_ID>@minDetail_ID