@EndPoint ) SELECT ASSY.Level ,ASSY.Find --,ASSY.Owner ,ASSY.Type ,ASSY.Number ,ASSY.Iteration --,ASSY.ReleaseStatus ,ASSY.Description ,ASSY.Quantity ,ASSY.RefDes ,ASSY.Notes ,Coalesce(S1.OwnerShort,'') AS Source1 ,Coalesce(S1.Number,'') AS SrcNumber1 ,Coalesce(S2.OwnerShort,'') AS Source2 ,Coalesce(S2.Number,'') AS SrcNumber2 ,Coalesce(S3.OwnerShort,'') AS Source3 ,Coalesce(S3.Number,'') AS SrcNumber3 FROM ( SELECT -- BOM with source pivot Level ,Find ,Owner ,Type ,Number ,Iteration ,ReleaseStatus ,Description ,Quantity ,RefDes ,Notes ,Src001 ,Src002 ,Src003 ,SortBy FROM ( SELECT -- parent assembly '0' AS [Level] ,' ' AS [POwner] ,' ' AS [PType] ,' ' AS [PNumber] ,' ' AS [Find] ,[Owner] AS [Owner] ,[Type] AS [Type] ,[Number] AS [Number] ,[Revision] + ' (' + [Lifecycle] + ')' AS [Iteration] ,[ReleaseStatus] ,'1.0000 ' + [DefaultUoM] AS [Quantity] ,[Description] AS [Description] ,'' AS [RefDes] ,'' AS [Notes] ,'' AS [SrcRank] ,[SrcItemId] ,'000' AS [SortBy] FROM ( SELECT It.Id AS [ItemId] ,ItVw.OwnerShort AS [Owner] ,ItVw.TypeShort AS [Type] ,It.ItemIdentifier AS [Number] ,It.[Name] AS [Description] ,Rv.[Id] AS [RevId] ,Rv.[RevisionIdentifier] AS [Revision] ,RvLC.Name AS [Lifecycle] ,@EmptyGuid AS [SrcItemId] ,ItUom.Name AS DefaultUoM ,Rv.[FromNum] AS ActiveFrom ,Rv.[ToNum] AS ActiveTo ,CASE Rv.RevState WHEN 0 THEN 'Pending' WHEN 1 THEN 'Releasing' WHEN 2 THEN 'Released' WHEN 3 THEN 'Canceling' WHEN 4 THEN 'Canceled' ELSE 'Unknown' END AS [ReleaseStatus] FROM PDXpertDB.dbo.[BasicItemRevision_View] Rv INNER JOIN PDXpertDB.dbo.[BasicItem_View] It ON It.Id = Rv.ItemId INNER JOIN PDXpertDB.viewer.[ItemView] ItVw ON ItVw.ItemId = It.[Id] INNER JOIN PDXpertDB.dbo.[BusinessObject_View] ItUom ON ItUom.Id = It.PartUOM INNER JOIN PDXpertDB.dbo.[BusinessObject_View] RvLC ON RvLC.Id = Rv.LifeCyclePhase WHERE Rv.Id = @P1 ) AS Parent UNION ALL SELECT -- child component with ranked sources CAST(BOM.[Level] AS nvarchar(10)) AS [Level] ,PrntItm.OwnerShort AS [POwner] ,PrntItm.TypeShort AS [PType] ,PrntItm.Number AS [PNumber] ,Partlist.[Number] AS [Find] ,ChldItm.OwnerShort AS [Owner] ,ChldItm.TypeShort AS [Type] ,ChldItm.Number AS [Number] ,ChldRev.[RevisionIdentifier] + ' (' + ChldRvLC.[Name] +')' AS [Iteration] ,CASE ChldRev.RevState WHEN 0 THEN 'Pending' WHEN 1 THEN 'Releasing' WHEN 2 THEN 'Released' WHEN 3 THEN 'Canceling' WHEN 4 THEN 'Canceled' ELSE 'Unknown' END AS [ReleaseStatus] ,Cast(Convert(Decimal(18, 4), Partlist.[Quantity]) AS varchar(20)) + ' ' + Coalesce(Unt.[Name],ChldItm.[DefaultUOM]) ,ChldItm.[Description] AS [Description] ,Partlist.[ReferenceDesignator] AS [RefDes] ,Partlist.[Notes] AS [Notes] ,ChldSrc.[SrcRank] ,ChldSrc.[SrcItemId] AS SrcItemId ,BOM.TreeId AS [SortBy] FROM PDXpertDB.dbo.StructureItem_View AS Partlist INNER JOIN BOM ON BOM.Id=Partlist.Id AND Partlist.FromNum <= @EndPoint AND Partlist.ToNum > @EndPoint LEFT JOIN PDXpertDB.dbo.BusinessObject AS Unt ON Unt.Id = Partlist.UoM INNER JOIN PDXpertDB.viewer.ItemMasterView AS PrntItm ON PrntItm.ItemId = Partlist.ParentItem INNER JOIN PDXpertDB.viewer.ItemMasterView AS ChldItm ON ChldItm.ItemId = Partlist.BasicItem INNER JOIN PDXpertDB.dbo.BasicItemRevision_View AS ChldRev ON ChldRev.ItemId = ChldItm.ItemId AND ChldRev.FromNum <= @EndPoint AND ChldRev.ToNum > @EndPoint INNER JOIN PDXpertDB.dbo.[BusinessObject_View] AS ChldRvLC ON ChldRvLC.Id = ChldRev.LifeCyclePhase LEFT JOIN ChildSource ChldSrc ON ChldSrc.ParentItem=ChldItm.ItemId ) BOMSRC PIVOT ( Min([SrcItemId]) FOR [SrcRank] IN ([Src001], [Src002], [Src003]) ) AS PVT ) AS ASSY LEFT JOIN viewer.ItemView S1 ON S1.ItemId=Src001 LEFT JOIN viewer.ItemView S2 ON S2.ItemId=Src002 LEFT JOIN viewer.ItemView S3 ON S3.ItemId=Src003 ORDER BY SortBy ]]> Table2