How to Sort a Hierarchy structure with a Sort Order Column

Question

I have a hierarchy structure with some dummy data in SQL:

HierarchyID ParentItemID SortOrder Name
1           NULL         99        Main Parent
2           1            80        Sub Parent 1
3           1            99        Sub Parent 2
4           2            99        Sub Parent 1 child 1
5           3            1         Sub Parent 2 child 1
6           2            89        Sub Parent 1 child 2

How can I sort this whereby the sort order drives the sorting without it affecting the logic of the hierarchy structure?

For example if the above was just sorted by SortOrder the hierarchy itself would not make sense. Sub Parent 1 child 2 would appear above Main Parent which is not allowed.

We are displaying this as a visual to a user. This user must be allowed to sort in which order the hierarchy appears without it affecting the logic of the hierarchy.

I Have created the following CTE that builds up a treeview:

WITH EntitiesCTE(ReportingHierarchyTypeID, ReportingHierarchyTypeCode, ReportingHierarchyItemID, Parent, Level, UltimateParent, HasChildren, Treepath, IsActive,ReportingHierarchySortOrder) AS
    ( SELECT    RHT.ReportingHierarchyTypeID,
                RHT.ReportingHierarchyTypeCode,
                RHI.ReportingHierarchyItemID AS id,
                RHI.ParentItemID, 
                0 AS Level,
                RHI.ReportingHierarchyItemID as UltimateParent,
                CASE
                    WHEN RHI.ReportingHierarchyItemID in (select t.ParentItemID FROM MASTER.ReportingHierarchyItem t) THEN 1
                    ELSE 0
                END AS HasChildren,
                CAST(RHI.ItemCode AS VARCHAR(1024)) AS Treepath,
                RHI.IsActive,
                RHI.ReportingHierarchySortOrder
      FROM MASTER.ReportingHierarchyItem RHI
      INNER JOIN  MASTER.ReportingHierarchyType RHT
      on RHI.ReportingHierarchyTypeID = RHT.ReportingHierarchyTypeID
      WHERE RHI.ParentItemID is null



      UNION ALL



        SELECT    RHT.ReportingHierarchyTypeID,
                RHT.ReportingHierarchyTypeCode,
                RHI.ReportingHierarchyItemID AS id, 
                RHI.ParentItemID,
                EntitiesCTE.Level + 1 AS Level,
                EntitiesCTE.UltimateParent,
                CASE
                    WHEN RHI.ReportingHierarchyItemID in (select t.ParentItemID FROM MASTER.ReportingHierarchyItem t) THEN 1
                    ELSE 0
                END AS HasChildren,
                CAST(EntitiesCTE.treepath + ' -> ' + CAST(RHI.ItemCode AS VARCHAR(1024)) AS VARCHAR(1024)) AS treepath,
                RHI.IsActive,
                RHI.ReportingHierarchySortOrder
      FROM MASTER.ReportingHierarchyItem RHI
      INNER JOIN  MASTER.ReportingHierarchyType RHT
      on RHI.ReportingHierarchyTypeID = RHT.ReportingHierarchyTypeID
      INNER JOIN EntitiesCTE
            ON EntitiesCTE.ReportingHierarchyItemID = RHI.ParentItemID
            )



SELECT
    a.*
    ,b.ReportingHierarchyTypeName
    ,e.ItemCode
    ,e.ItemName
    ,e.ParentItemID
    ,e.CompanyID
--    ,e.ReportingHierarchySortOrder




    FROM EntitiesCTE a



INNER JOIN 
    MASTER.ReportingHierarchyType b
    ON  b.ReportingHierarchyTypeID = a.ReportingHierarchyTypeID
INNER JOIN MASTER.ReportingHierarchyItem e
    ON e.ReportingHierarchyItemID = a.ReportingHierarchyItemID
    WHERE a.ReportingHierarchyTypeID = e.ReportingHierarchyTypeID 
    --ORDER BY a.Treepath,a.ReportingHierarchySortOrder, Coalesce(a.parent,0)
    ORDER BY a.Treepath, a.ReportingHierarchySortOrder ASC

However I get the following:

HierarchyID ParentItemID SortOrder Name
1           NULL         99        Main Parent
2           1            80        Sub Parent 1
4           2            99        Sub Parent 1 child 1
6           2            89        Sub Parent 1 child 2
3           1            99        Sub Parent 2
5           3            1         Sub Parent 2 child 1

For this to be correct HierarchyID 4 and 6 should be swapped around.

Build a `sortPath` of `SortOrder` column the same way as `treepath` is built of `RHI.ItemCode`. `ORDER BY sortPath` — Serg, Aug 21 '19 at 18:28
Thanks! I added: CAST(RHI.ReportingHierarchySortOrder AS VARCHAR(1024)) AS ReportingHierarchySortOrderPath --before the Union CAST(EntitiesCTE.ReportingHierarchySortOrderPath + ' -> ' + CAST(RHI.ReportingHierarchySortOrder AS VARCHAR(1024)) AS VARCHAR(1024)) AS ReportingHierarchySortOrderPath --after the Union ORDER BY a.ReportingHierarchySortOrderPath ASC --at the end And it Worked! — RJ Oosthuizen, Aug 21 '19 at 20:44
This only works with entries that have a sort order below 100. Is there away to incorporate sort orders above 100? At the moment:99 -> 80 -> 100 appears before 99 -> 80 -> 89 — RJ Oosthuizen, Aug 26 '19 at 08:40
Suppose all `RHI.ReportingHierarchySortOrder` < 999999. Use kind of `1000000+RHI.ReportingHierarchySortOrder` , this way any casted value will be of 7 chars length and the lexicographical order when casted to strings will follow the order of original integers. — Serg, Aug 26 '19 at 13:17

score 0 · Answer 1 · answered Aug 21 '19 at 20:48

I adapted the Treeview to the Following and it Worked:

WITH EntitiesCTE(ReportingHierarchyTypeID, ReportingHierarchyTypeCode, ReportingHierarchyItemID, Parent, Level, UltimateParent, HasChildren, Treepath, IsActive,ReportingHierarchySortOrder, ReportingHierarchySortOrderPath) AS
    ( SELECT    RHT.ReportingHierarchyTypeID,
                RHT.ReportingHierarchyTypeCode,
                RHI.ReportingHierarchyItemID AS id,
                RHI.ParentItemID, 
                0 AS Level,
                RHI.ReportingHierarchyItemID as UltimateParent,
                CASE
                    WHEN RHI.ReportingHierarchyItemID in (select t.ParentItemID FROM MASTER.ReportingHierarchyItem t) THEN 1
                    ELSE 0
                END AS HasChildren,
                CAST(RHI.ItemCode AS VARCHAR(1024)) AS Treepath,
                RHI.IsActive,
                RHI.ReportingHierarchySortOrder,
                CAST(RHI.ReportingHierarchySortOrder AS VARCHAR(1024)) AS ReportingHierarchySortOrderPath
      FROM MASTER.ReportingHierarchyItem RHI
      INNER JOIN  MASTER.ReportingHierarchyType RHT
      on RHI.ReportingHierarchyTypeID = RHT.ReportingHierarchyTypeID
      WHERE RHI.ParentItemID is null



      UNION ALL 



        SELECT    RHT.ReportingHierarchyTypeID,
                RHT.ReportingHierarchyTypeCode,
                RHI.ReportingHierarchyItemID AS id, 
                RHI.ParentItemID,
                EntitiesCTE.Level + 1 AS Level,
                EntitiesCTE.UltimateParent,
                CASE
                    WHEN RHI.ReportingHierarchyItemID in (select t.ParentItemID FROM MASTER.ReportingHierarchyItem t) THEN 1
                    ELSE 0
                END AS HasChildren,
                CAST(EntitiesCTE.treepath + ' -> ' + CAST(RHI.ItemCode AS VARCHAR(1024)) AS VARCHAR(1024)) AS treepath,
                RHI.IsActive,
                RHI.ReportingHierarchySortOrder,
                CAST(EntitiesCTE.ReportingHierarchySortOrderPath + ' -> ' + CAST(RHI.ReportingHierarchySortOrder AS VARCHAR(1024)) AS VARCHAR(1024)) AS ReportingHierarchySortOrderPath
      FROM MASTER.ReportingHierarchyItem RHI
      INNER JOIN  MASTER.ReportingHierarchyType RHT
      on RHI.ReportingHierarchyTypeID = RHT.ReportingHierarchyTypeID
      INNER JOIN EntitiesCTE
            ON EntitiesCTE.ReportingHierarchyItemID = RHI.ParentItemID
            )



SELECT
    a.*
    ,b.ReportingHierarchyTypeName
    ,e.ItemCode
    ,e.ItemName
    ,e.ParentItemID
    ,e.CompanyID
--    ,e.ReportingHierarchySortOrder




    FROM EntitiesCTE a



INNER JOIN 
    MASTER.ReportingHierarchyType b
    ON  b.ReportingHierarchyTypeID = a.ReportingHierarchyTypeID
INNER JOIN MASTER.ReportingHierarchyItem e
    ON e.ReportingHierarchyItemID = a.ReportingHierarchyItemID
    WHERE a.ReportingHierarchyTypeID = e.ReportingHierarchyTypeID 
    ORDER BY a.ReportingHierarchySortOrderPath ASC

How to Sort a Hierarchy structure with a Sort Order Column

1 Answers1