sql server - SQL Recursive CTE 'where-used' / BOM explosion part 2 -
hi carries on post,
sql recursive cte 'where-used' / bom explosion
which original requirement answered have realised have final requirement. data have after '1' i.e. description etc., want repeat each level description correctly identifies correct parent item. tried adding columns in again in final select repeated items level 1. how can done?
* update * i'm struggling test data/query how want. want items relate each other through bomid. output img.
the relationship between bom , bomversion 1 item has many bomid's in bom table each bomid has corresponding record in bomversion through bomid different itemid off of bomversion. itemid can exist in bom table multiple bomids. know confusing , difficult demonstrate test data. that's why i'm happy put on bounty.
* update * through i've learnt i've redone query/test data. haven't been able want query/data may need tweaked or added. i'll output i'm expecting. when bom.itemid linked bv.itemid through bomid expect bv.itemid moved next level , if bom.itemid links bv.itemid move item next level etc. etc. along other info related item in level.
if object_id('tempdb..#bom') not null drop table #bom; create table #bom ( itemid nvarchar(10) , bomid nvarchar(10) , bomqty int , unitid nvarchar(10) , bomqtyserie int ); insert #bom ( itemid , bomid , bomqty , unitid , bomqtyserie ) values ( n'100001' , -- itemid - nvarchar(10) n'1a' , -- bomid - nvarchar(10) 10 , -- bomqty - int n'g' , -- unitid - nvarchar(10) 5 -- bomqtyserie - int ); insert #bom ( itemid , bomid , bomqty , unitid , bomqtyserie ) values ( n'100001' , -- itemid - nvarchar(10) n'2a' , -- bomid - nvarchar(10) 15 , -- bomqty - int n'kg' , -- unitid - nvarchar(10) 13 -- bomqtyserie - int ); insert #bom ( itemid , bomid , bomqty , unitid , bomqtyserie ) values ( n'100001' , -- itemid - nvarchar(10) n'3a' , -- bomid - nvarchar(10) 16 , -- bomqty - int n'l' , -- unitid - nvarchar(10) 16 -- bomqtyserie - int ); insert #bom ( itemid , bomid , bomqty , unitid , bomqtyserie ) values ( n'100002' , -- itemid - nvarchar(10) n'1a' , -- bomid - nvarchar(10) 18 , -- bomqty - int n'g' , -- unitid - nvarchar(10) 17 -- bomqtyserie - int ); insert #bom ( itemid , bomid , bomqty , unitid , bomqtyserie ) values ( n'100004' , -- itemid - nvarchar(10) n'2a' , -- bomid - nvarchar(10) 20 , -- bomqty - int n'kg' , -- unitid - nvarchar(10) 11 -- bomqtyserie - int ); insert #bom ( itemid , bomid , bomqty , unitid , bomqtyserie ) values ( n'100002' , -- itemid - nvarchar(10) n'2a' , -- bomid - nvarchar(10) 23 , -- bomqty - int n'kg' , -- unitid - nvarchar(10) 19 -- bomqtyserie - int ); insert #bom ( itemid , bomid , bomqty , unitid , bomqtyserie ) values ( n'100003' , -- itemid - nvarchar(10) n'2a' , -- bomid - nvarchar(10) 25 , -- bomqty - int n'kg' , -- unitid - nvarchar(10) 21 -- bomqtyserie - int ); if object_id('tempdb..#bomversion') not null drop table #bomversion; create table #bomversion ( itemid nvarchar(10) , bomid nvarchar(10) , name nvarchar(20) , active bit ); insert #bomversion ( itemid , bomid , name , active ) values ( n'100002' , -- itemid - nvarchar(10) n'1a' , -- bomid - nvarchar(10) n'100002 version' , -- name - nvarchar(10) 1 -- active - bit ); insert #bomversion ( itemid , bomid , name , active ) values ( n'100002' , -- itemid - nvarchar(10) n'2a' , -- bomid - nvarchar(10) n'100002.1 version' , -- name - nvarchar(10) 1 -- active - bit ); insert #bomversion ( itemid , bomid , name , active ) values ( n'100003' , -- itemid - nvarchar(10) n'3a' , -- bomid - nvarchar(10) n'100003 version' , -- name - nvarchar(10) 1 -- active - bit ); insert #bomversion ( itemid , bomid , name , active ) values ( n'100004' , -- itemid - nvarchar(10) n'4a' , -- bomid - nvarchar(10) n'100004 version' , -- name - nvarchar(10) 1 -- active - bit ); insert #bomversion ( itemid , bomid , name , active ) values ( n'100005' , -- itemid - nvarchar(10) n'5a' , -- bomid - nvarchar(10) n'100005 version' , -- name - nvarchar(10) 1 -- active - bit ); if object_id('tempdb..#inventtable') not null drop table #inventtable; create table #inventtable ( itemid nvarchar(10) , name nvarchar(20) , product int ); insert #inventtable ( itemid, name, product ) values ( n'100001', -- itemid - nvarchar(10) n'100001 name', -- name - nvarchar(10) 1 -- product - int ); insert #inventtable ( itemid, name, product ) values ( n'100002', -- itemid - nvarchar(10) n'100002 name', -- name - nvarchar(10) 2 -- product - int ); insert #inventtable ( itemid, name, product ) values ( n'100003', -- itemid - nvarchar(10) n'100003 name', -- name - nvarchar(10) 3 -- product - int ); insert #inventtable ( itemid, name, product ) values ( n'100004', -- itemid - nvarchar(10) n'100004 name', -- name - nvarchar(10) 4 -- product - int ); insert #inventtable ( itemid, name, product ) values ( n'100005', -- itemid - nvarchar(10) n'100005 name', -- name - nvarchar(10) 5 -- product - int ); if object_id('tempdb..#ecoresproducttranslation') not null drop table #ecoresproducttranslation; create table #ecoresproducttranslation ( product int , name nvarchar(20) ); insert #ecoresproducttranslation ( product, name ) values ( 1, -- product - int n'100001 description' -- name - nvarchar(10) ); insert #ecoresproducttranslation ( product, name ) values ( 2, -- product - int n'100002 description' -- name - nvarchar(10) ); insert #ecoresproducttranslation ( product, name ) values ( 3, -- product - int n'100003 description' -- name - nvarchar(10) ); insert #ecoresproducttranslation ( product, name ) values ( 4, -- product - int n'100004 description' -- name - nvarchar(10) ); insert #ecoresproducttranslation ( product, name ) values ( 5, -- product - int n'100005 description' -- name - nvarchar(10) ); cte ( select b.itemid mainitem , bv.name bvname , b.bomid , bv.itemid parentitem , ecpt.name parentitemname , b.bomqty , b.unitid , b.bomqtyserie , 1 [level] #bom b join #bomversion bv on bv.bomid = b.bomid join #inventtable on it.itemid = bv.itemid join #ecoresproducttranslation ecpt on ecpt.product = it.product b.itemid = '100001' , bv.active = 1 union select c.mainitem , c.bvname , c.bomid , bv.itemid , c.parentitemname , c.bomqty , c.unitid , c.bomqtyserie , c.[level] + 1 cte c join #bom b on c.parentitem = b.itemid join #bomversion bv on bv.bomid = b.bomid c.[level] <= 7 ) select mainitem , [1] level1 , bvname , parentitemname , bomqty , unitid , bomqtyserie , [2] level2 , bvname , parentitemname , bomqty , unitid , bomqtyserie , [3] level3 , bvname , parentitemname , bomqty , unitid , bomqtyserie , [4] level4 , bvname , parentitemname , bomqty , unitid , bomqtyserie , [5] level5 , bvname , parentitemname , bomqty , unitid , bomqtyserie , [6] level6 , bvname , parentitemname , bomqty , unitid , bomqtyserie , [7] level7 , bvname , parentitemname , bomqty , unitid , bomqtyserie cte pivot ( max(parentitem) [level] in ( [1], [2], [3], [4], [5], [6], [7] ) ) pvt; 
here version easy understand , maintain:
; cte ( select b.recid mainid , b.itemid mainitem , bv.name , bv.itemid parentitem , ecpt.name parentitemname , b.bomqty , b.unitid , b.bomqtyserie , 0 [level] #bom b join #bomversion bv on bv.bomid = b.bomid join #inventtable on it.itemid = bv.itemid join #ecoresproducttranslation ecpt on ecpt.product = it.product b.itemid = '113621' , bv.active = '1' union select c.mainid , c.mainitem , c.name , bv.itemid , c.parentitemname , c.bomqty , c.unitid , c.bomqtyserie , c.[level] + 1 cte c join #bom b on c.parentitem = b.itemid join #bomversion bv on bv.bomid = b.bomid c.[level] <= 6 ) select b.itemid , c1.parentitem parentitem1 , c1.name bvname1 , c1.parentitemname parentitemname1 , c1.bomqty bomqty1 , c1.unitid unitid1 , c1.bomqtyserie bomqtyserie1 , c2.parentitem parentitem2 , c2.name bvname2 , c2.parentitemname parentitemname2 , c2.bomqty bomqty2 , c2.unitid unitid2 , c2.bomqtyserie bomqtyserie2 , c3.parentitem parentitem3 , c3.name bvname3 , c3.parentitemname parentitemname3 , c3.bomqty bomqty3 , c3.unitid unitid3 , c3.bomqtyserie bomqtyserie3 , c4.parentitem parentitem2 , c4.name bvname2 , c4.parentitemname parentitemname4 , c4.bomqty bomqty4 , c4.unitid unitid4 , c4.bomqtyserie bomqtyserie4 , c5.parentitem parentitem5 , c5.name bvname5 , c5.parentitemname parentitemname5 , c5.bomqty bomqty5 , c5.unitid unitid5 , c5.bomqtyserie bomqtyserie5 , c6.parentitem parentitem6 , c6.name bvname6 , c6.parentitemname parentitemname6 , c6.bomqty bomqty6 , c6.unitid unitid6 , c6.bomqtyserie bomqtyserie6 , c7.parentitem parentitem7 , c7.name bvname7 , c7.parentitemname parentitemname7 , c7.bomqty bomqty7 , c7.unitid unitid7 , c7.bomqtyserie bomqtyserie7 #bom b left join cte c1 on b.recid = c1.mainid , c1.[level] = 0 left join cte c2 on b.recid = c2.mainid , c2.[level] = 1 left join cte c3 on b.recid = c3.mainid , c3.[level] = 2 left join cte c4 on b.recid = c4.mainid , c4.[level] = 3 left join cte c5 on b.recid = c5.mainid , c5.[level] = 4 left join cte c6 on b.recid = c6.mainid , c6.[level] = 5 left join cte c7 on b.recid = c7.mainid , c7.[level] = 6 b.itemid = '113621' , c1.parentitem not null; edit:
input:
insert #bom values ( 1, n'10', n'1a' ), ( 2, n'20', n'2a' ), ( 3, n'30', n'3a' ), ( 4, n'40', n'4a' ), ( 5, n'50', n'5a' ), ( 6, n'60', n'6a' ), ( 7, n'70', n'7a' ), ( 8, n'80', n'8a' ), ( 9, n'90', n'9a' ), ( 10, n'100', n'10a' ), ( 11, n'110', n'11a' ) insert #bomversion values ( 1, n'20', n'10 pre', n'1a' ), ( 2, n'30', n'20 pre', n'2a' ), ( 3, n'40', n'30 pre', n'3a' ), ( 4, n'50', n'40 pre', n'4a' ), ( 5, n'60', n'50 pre', n'5a' ), ( 6, n'70', n'60 pre', n'6a' ), ( 7, n'80', n'70 pre', n'7a' ), ( 8, n'100', n'90 pre', n'9a' ), ( 9, n'110', n'100 pre', n'10a' ), ( 9, n'120', n'110 pre', n'11a' ) insert #item values ( 1, n'10', n'10 desc' ), ( 2, n'20', n'20 desc' ), ( 3, n'30', n'30 desc' ), ( 4, n'40', n'40 desc' ), ( 5, n'50', n'50 desc' ), ( 6, n'60', n'60 desc' ), ( 7, n'70', n'70 desc' ), ( 8, n'80', n'80 desc' ), ( 9, n'90', n'90 desc' ), ( 10, n'100', n'100 desc' ), ( 11, n'110', n'110 desc' ) output:
Comments
Post a Comment