Recursive query to explode a BOM

• sivaj2k

  Ten Centuries

  Points: 1188

  More actions

  July 3, 2014 at 7:55 am

  #1726825

  Hi,

  As per your input the other sets should come like

  2000022, 2001770, 2001771

  2000022, 2001527, 2001528

  Above result set dosen't have 2002005. so no result

  -----

  declare @bom table ([BOM Number] varchar(max),[Mat Number] varchar(max))

  insert into @bom

  select '2000022' [BOM Number],'2001770' [Mat Number] union

  select '2000022','2003496' union

  select '2000022','2001527' union

  select '2000022','2003495' union

  select '2000246','2002005' union

  select '2000506','2000246' union

  select '2000837','2000246' union

  select '2001527','2001528' union

  select '2001533','2000246' union

  select '2001770','2001771' union

  select '2001780','2000246' union

  select '2002292','2000506' union

  select '2002395','2002292' union

  select '2002565','2000837' union

  select '2002566','2002565' union

  select '2002875','2002566' union

  select '2003495','2002875' union

  select '2003496','2002395'

  ;WITH maBOM (PIECE, STEPS, CHEMIN)

  AS

  (SELECT DISTINCT BOM3.[BOM Number], 0, CAST('2000022' AS VARCHAR(MAX))

  FROM @bom BOM3

  where [BOM Number]=2000022

  UNION ALL

  SELECT [Mat Number], b1.STEPS+1, cast(b1.CHEMIN as varchar(max))+', '+cast(b2.[Mat Number] as varchar(max))

  FROM @bom AS b2

  INNER JOIN maBOM AS b1

  ON b1.PIECE = b2.[BOM Number])

  select * into #temp from maBOM

  select * from #temp where piece=2002005

  select o.CHEMIN from #temp o join (

  select a.CHEMIN,max(b.STEPS) step

  from #temp a , #temp b where a.steps = 1 and

  b.CHEMIN like a.CHEMIN+'%'

  group by a.CHEMIN)b on o.STEPS = b.step and

  o.CHEMIN like b.CHEMIN+'%'

  drop table #temp

  Regards

  Siva Kumar J.