CTE BOM query Ordering

Question

I am working on a BOM (for Documents so I'm calling a BOD)and I would like to end up with the data in a tree view so I need to have the BOM explosion and sorted with each Level. I cannot figure out how to get the sorting to work correctly

Im working in SSMS

I have a View that holds; [ParentDocumentTitle], [ParentDocumentName], [ChildDocumentTitle], [ChildDocumentName], [PDFFilePath], all Nvarchar

Then I have SQL Query

--alter procedure [dbo].[GetBODSummaryLevels]


IF EXISTS(SELECT [name] FROM tempdb.sys.tables WHERE [name] like '#BODSummary%') 
BEGIN
   DROP TABLE #BODSummary;
END;

DECLARE
@ParentDocTitle nvarchar(50),
@level int

SET @ParentDocTitle = 'Test Book';
SET @level = 10;

--as 
begin
WITH RPL (
[Level],
[ParentDocumentTitle], 
[ParentDocumentName],
[ChildDocumentTitle], 
[ChildDocumentName], 
[PDFFilePath]) 

AS
     (  SELECT 
            0,
            ROOT.[ParentDocumentTitle],
            ROOT.[ParentDocumentName], 
            ROOT.[ChildDocumentTitle], 
            ROOT.[ChildDocumentName],
            ROOT.[PDFFilePath]
        FROM [WorkInstruictionManagerDB].[dbo].[BOD] ROOT
        WHERE ROOT.[ParentDocumentTitle] = @ParentDocTitle
      UNION ALL
        SELECT 
            Parent.Level+1,
            CHILD.[ParentDocumentTitle], 
            CHILD.[ParentDocumentName], 
            CHILD.[ChildDocumentTitle], 
            CHILD.[ChildDocumentName], 
            CHILD.[PDFFilePath]
        FROM RPL PARENT, [WorkInstruictionManagerDB].[dbo].[BOD] CHILD
        WHERE  PARENT.[ChildDocumentTitle] = CHILD.[ParentDocumentTitle]and PARENT.Level<@level
     )

SELECT 
[Level] as bodlevel,
[ParentDocumentTitle] as pt, 
[ParentDocumentName] as pn, 
[ChildDocumentTitle] as ct,
[ChildDocumentName] as cn,
[PDFFilePath] as pdf

into #BODSummary
FROM RPL
Select * From #BODSummary 
end

This provides the correct information but I cannot figure out how to sort that information so the children show up below the parent.

What am I missing or just flat out doing wrong?

Answer 1

Build an hierachy path and sort by it. Kind of

WITH RPL (
[Level],
[ParentDocumentTitle], 
[ParentDocumentName],
[ChildDocumentTitle], 
[ChildDocumentName], 
[PDFFilePath],
hierarchypath ) 

AS
     (  SELECT 
            0,
            ROOT.[ParentDocumentTitle],
            ROOT.[ParentDocumentName], 
            ROOT.[ChildDocumentTitle], 
            ROOT.[ChildDocumentName],
            ROOT.[PDFFilePath],
            convert(varchar(max), ROOT.[ChildDocumentTitle]) 
        FROM [WorkInstruictionManagerDB].[dbo].[BOD] ROOT
        WHERE ROOT.[ParentDocumentTitle] = @ParentDocTitle
      UNION ALL
        SELECT 
            Parent.Level+1,
            CHILD.[ParentDocumentTitle], 
            CHILD.[ParentDocumentName], 
            CHILD.[ChildDocumentTitle], 
            CHILD.[ChildDocumentName], 
            CHILD.[PDFFilePath],
            hierarchypath  + '->' + CHILD.[ParentDocumentTitle]
        FROM RPL PARENT
        JOIN [WorkInstruictionManagerDB].[dbo].[BOD] CHILD
          ON  PARENT.[ChildDocumentTitle] = CHILD.[ParentDocumentTitle] and PARENT.Level<@level
     )

SELECT 
[Level] as bodlevel,
[ParentDocumentTitle] as pt, 
[ParentDocumentName] as pn, 
[ChildDocumentTitle] as ct,
[ChildDocumentName] as cn,
[PDFFilePath] as pdf,
hierarchypath
into #BODSummary
FROM RPL;

Select * 
From #BODSummary 
order by hierarchypath desc -- children first