if object_id('tempdb..#Tmp_iBomDetTbl') is not null drop table #Tmp_iBomDetTbl CREATE TABLE #Tmp_iBomDetTbl ( Detail_ID int identity(1,1), SubItem_No varchar(50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL, ParentItem_No varchar(50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL, Cost1 int, cost2 int) 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 Create TABLE #Tmp_ExpandBom ( Level_No int, Level_Desc varchar(100) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL, Item_No varchar(50)COLLATE Chinese_Taiwan_Stroke_CI_AS NULL, Cost1 int, Cost2 int, xPath varchar(1000) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ) DECLARE @Level_No int SET @Level_No=1 --先加入最上層的一條記錄, 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 EndDECLARE @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 End Select * From #Tmp_ExpandBom
动态生成层级字段
最新推荐文章于 2023-06-02 11:11:33 发布