DataGrid @EndPoint ) SELECT ASSY.[Level] ,ASSY.[Find] --,ASSY.[Owner] ,ASSY.[Type] ,ASSY.[Number] AS [Number] ,[OpenItemId] AS [HK100] ,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] ,Coalesce(S4.[OwnerShort],'') AS [Source4] ,Coalesce(S4.[Number],'') AS [SrcNumber4] ,Coalesce(S5.[OwnerShort],'') AS [Source5] ,Coalesce(S5.[Number],'') AS [SrcNumber5] FROM ( SELECT -- BOM with source pivot [Level] ,[Find] ,[Owner] ,[Type] ,[Number] ,[Iteration] ,[ReleaseStatus] ,[Description] ,[Quantity] ,[RefDes] ,[Notes] ,[Src001] ,[Src002] ,[Src003] ,[Src004] ,[Src005] ,[SortBy] ,[OpenItemId] 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] ,@EmptyGuid AS [SrcItemId] ,'000' AS [SortBy] ,[ItemId] AS [OpenItemId] 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] ,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 [BasicItemRevision_View] Rv INNER JOIN [BasicItem_View] It ON It.[Id] = Rv.[ItemId] INNER JOIN viewer.[ItemView] ItVw ON ItVw.[ItemId] = It.[Id] INNER JOIN [BusinessObject_View] ItUom ON ItUom.[Id] = It.PartUOM INNER JOIN [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] ,ChldItm.[ItemId] AS [OpenItemId] FROM StructureItem_View AS Partlist INNER JOIN BOM ON BOM.Id=Partlist.Id AND Partlist.FromNum <= @EndPoint AND Partlist.ToNum > @EndPoint LEFT JOIN BusinessObject AS Unt ON Unt.Id = Partlist.UoM INNER JOIN viewer.ItemMasterView AS PrntItm ON PrntItm.ItemId = Partlist.ParentItem INNER JOIN viewer.ItemMasterView AS ChldItm ON ChldItm.ItemId = Partlist.BasicItem INNER JOIN BasicItemRevision_View AS ChldRev ON ChldRev.ItemId = ChldItm.ItemId AND ChldRev.FromNum <= @EndPoint AND ChldRev.ToNum > @EndPoint INNER JOIN [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], [Src004], [Src005]) ) 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 LEFT JOIN viewer.ItemView S4 ON S4.ItemId=Src004 LEFT JOIN viewer.ItemView S5 ON S5.ItemId=Src005 ORDER BY SortBy ]]>