[易飞]物料反展

  -- =============================================   -- Author: <David Gong>   -- Create date: <2013-03-20>   -- Description: <物料反展 参考邹健>   -- =============================================  ALTER   function   UF_ParentItem(@item   varchar(30))     returns   @ParentItem   table(ParentItem   varchar(30),[level]   int,ProcessedItem   varchar(8000) )     as     begin     declare   @l   int     set   @l=1     insert   @ParentItem   select   MD001,@l,MD001     from   BOMMD  a     where   MD003=@item     while   @@rowcount>0     begin     set   @l=@l+1     insert   @ParentItem   select   a.MD001,@l,rtrim(a.MD001)+' -> '+b.ProcessedItem     from   BOMMD a,@ParentItem  b     where   a.MD003=b.ParentItem   and   b.[level]=@l-1     end     update   @ParentItem   set   level=@l-level     return     end   --顶层  select   *   from   dbo.UF_ParentItem('31302002') WHERE level=1--第二层select   *   from   dbo.UF_ParentItem('31302002') WHERE level=2--所有  select   *   from   dbo.UF_ParentItem('31302002')