Back to List

Sorting Results in the Flattened 7-layer Recursive Hierarchy Salad

Bob Charapata Bob Charapata  |  
Apr 16, 2019
 

In my previous article about Flattening a Recursive Hierarchy, I wrote about an approach that transforms existing recursive hierarchies into usable data constructs for analytics. This post builds on that article to show how to display the results in correct hierarchical order.

After flattening the recursive hierarchy, the query results did not display the data in proper hierarchical order. If this data is being used for display purposes, it is necessary to be sure to sort the hierarchy.

Add an "Order By" statement for the Path column to the final query.

WITH [HierarchyDepth]([ID],
                      [ParentID],
                      [HierarchyName],
                      [SortID],
                      [Path],
                      [Depth])
     AS (SELECT [rh].[ID],
                [rh].[ParentID],
                [rh].[HierarchyName],
                [rh].[SortID],
                CAST([rh].[ID] AS varchar(1000)),
                0
         FROM @RecursiveHierarchy AS [rh]
         WHERE [rh].[ParentID] IS NULL
         UNION ALL
         SELECT [rh].[ID],
                [rh].[ParentID],
                [rh].[HierarchyName],
                [rh].[SortID],
                CAST([dh].[Path] + '|' + CAST([rh].[ID] AS varchar(1000)) AS varchar(1000)),
                [dh].[Depth] + 1
         FROM @RecursiveHierarchy AS [rh]
         JOIN [HierarchyDepth] AS [dh]
         ON [dh].[ID] = [rh].[ParentID]),
     [AllAncestor]([ID],
                   [HierarchyName],
                   [ParentID],
                   [Depth])
     AS (SELECT [dh].[ID],
                [dh].[HierarchyName],
                [dh].[ParentID],
                [dh].[Depth]
         FROM [HierarchyDepth] AS [dh]
         UNION ALL
         SELECT [aa].[ID],
                [rh].[HierarchyName],
                [rh].[ParentID],
                [aa].[Depth] - 1
         FROM [AllAncestor] AS [aa]
         JOIN @RecursiveHierarchy AS [rh]
         ON [rh].[ID] = [aa].[ParentID])
     SELECT [pn].[ID],
            REPLICATE('- ', [hd].[Depth]) + [hd].[HierarchyName] AS [HierarchyName],
            [hd].[SortID],
            [hd].[Path],
            [hd].[Depth],
            [pn].[0] AS [Root],
            [pn].[1] AS [Level1],
            [pn].[2] AS [Level2],
            [pn].[3] AS [Level3],
            [pn].[4] AS [Level4],
            [pn].[5] AS [Level5],
            [pn].[6] AS [Level6],
            [pn].[7] AS [Level7]
     FROM
     (
         SELECT [aa].[ID],
                [aa].[HierarchyName],
                [aa].[Depth]
         FROM [AllAncestor] AS [aa]
     ) AS [hn] PIVOT(MAX([hn].[HierarchyName]) FOR [hn].[Depth] IN([0],
                                                                   [1],
                                                                   [2],
                                                                   [3],
                                                                   [4],
                                                                   [5],
                                                                   [6],
                                                                   [7])) AS [pn]
     JOIN [HierarchyDepth] AS [hd]
     ON [pn].[ID] = [hd].[ID]
     ORDER BY [hd].[Path];

The hierarchy structure now shows every child under the correct parent. Notice that SortID is also added to the result set to show how the hierarchy structure differs from the sort order.

Compare the SortID to the order displayed at each level. Notice that rows at the same level are not ordered according to that SortID. Add a calculated SortPath column (like the Depth column) to the recursive CTE. Also, change the "ORDER BY" to use the new SortPath column on the end of the select query. This causes every level of the hiearchy to sort.

WITH [HierarchyDepth]([ID],
                      [ParentID],
                      [HierarchyName],
                      [SortID],
                      [Path],
                      [SortPath],
                      [Depth])
     AS (SELECT [rh].[ID],
                [rh].[ParentID],
                [rh].[HierarchyName],
                [rh].[SortID],
                CAST([rh].[ID] AS varchar(1000)),
                CAST([rh].[SortID] AS varchar(1000)),
                0
         FROM @RecursiveHierarchy AS [rh]
         WHERE [rh].[ParentID] IS NULL
         UNION ALL
         SELECT [rh].[ID],
                [rh].[ParentID],
                [rh].[HierarchyName],
                [rh].[SortID],
                CAST([dh].[Path] + '|' + CAST([rh].[ID] AS varchar(1000)) AS varchar(1000)),
                CAST([dh].[SortPath] + '|' + CAST([rh].[SortID] AS varchar(1000)) AS varchar(1000)),
                [dh].[Depth] + 1
         FROM @RecursiveHierarchy AS [rh]
         JOIN [HierarchyDepth] AS [dh]
         ON [dh].[ID] = [rh].[ParentID]),
     [AllAncestor]([ID],
                   [HierarchyName],
                   [ParentID],
                   [Depth])
     AS (SELECT [dh].[ID],
                [dh].[HierarchyName],
                [dh].[ParentID],
                [dh].[Depth]
         FROM [HierarchyDepth] AS [dh]
         UNION ALL
         SELECT [aa].[ID],
                [rh].[HierarchyName],
                [rh].[ParentID],
                [aa].[Depth] - 1
         FROM [AllAncestor] AS [aa]
         JOIN @RecursiveHierarchy AS [rh]
         ON [rh].[ID] = [aa].[ParentID])
     SELECT [pn].[ID],
            REPLICATE('- ', [hd].[Depth]) + [hd].[HierarchyName] AS [HierarchyName],
            [hd].[SortID],
            [hd].[Path],
            [hd].[SortPath],
            [hd].[Depth],
            [pn].[0] AS [Root],
            [pn].[1] AS [Level1],
            [pn].[2] AS [Level2],
            [pn].[3] AS [Level3],
            [pn].[4] AS [Level4],
            [pn].[5] AS [Level5],
            [pn].[6] AS [Level6],
            [pn].[7] AS [Level7]
     FROM
     (
         SELECT [aa].[ID],
                [aa].[HierarchyName],
                [aa].[Depth]
         FROM [AllAncestor] AS [aa]
     ) AS [hn] PIVOT(MAX([hn].[HierarchyName]) FOR [hn].[Depth] IN([0],
                                                                   [1],
                                                                   [2],
                                                                   [3],
                                                                   [4],
                                                                   [5],
                                                                   [6],
                                                                   [7])) AS [pn]
     JOIN [HierarchyDepth] AS [hd]
     ON [pn].[ID] = [hd].[ID]
     ORDER BY [hd].[SortPath];

Here is the final result set:

ID   HierarchyName            SortID  Path                   SortPath          Depth  Root  Level1     Level2     Level3     Level4     Level5     Level6     Level7
---- ------------------------ ------- ---------------------- ----------------- ------ ----- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1    R1                       1       1                      1                 0      R1    NULL       NULL       NULL       NULL       NULL       NULL       NULL
5    - R1 L1 I1               1       1|5                    1|1               1      R1    R1 L1 I1   NULL       NULL       NULL       NULL       NULL       NULL
9    - - R1 L2 I1             1       1|5|9                  1|1|1             2      R1    R1 L1 I1   R1 L2 I1   NULL       NULL       NULL       NULL       NULL
12   - - - R1 L3 I1           1       1|5|9|12               1|1|1|1           3      R1    R1 L1 I1   R1 L2 I1   R1 L3 I1   NULL       NULL       NULL       NULL
15   - - - - R1 L4 I1         1       1|5|9|12|15            1|1|1|1|1         4      R1    R1 L1 I1   R1 L2 I1   R1 L3 I1   R1 L4 I1   NULL       NULL       NULL
17   - - - - - R1 L5 I1       1       1|5|9|12|15|17         1|1|1|1|1|1       5      R1    R1 L1 I1   R1 L2 I1   R1 L3 I1   R1 L4 I1   R1 L5 I1   NULL       NULL
18   - - - - - - R1 L6 I1     1       1|5|9|12|15|17|18      1|1|1|1|1|1|1     6      R1    R1 L1 I1   R1 L2 I1   R1 L3 I1   R1 L4 I1   R1 L5 I1   R1 L6 I1   NULL
20   - - - - - - - R1 L7 I1   1       1|5|9|12|15|17|18|20   1|1|1|1|1|1|1|1   7      R1    R1 L1 I1   R1 L2 I1   R1 L3 I1   R1 L4 I1   R1 L5 I1   R1 L6 I1   R1 L7 I1
19   - - - - - - - R1 L7 I2   2       1|5|9|12|15|17|18|19   1|1|1|1|1|1|1|2   7      R1    R1 L1 I1   R1 L2 I1   R1 L3 I1   R1 L4 I1   R1 L5 I1   R1 L6 I1   R1 L7 I2
16   - - - - - R1 L5 I2       2       1|5|9|12|15|16         1|1|1|1|1|2       5      R1    R1 L1 I1   R1 L2 I1   R1 L3 I1   R1 L4 I1   R1 L5 I2   NULL       NULL
14   - - - - R1 L4 I2         2       1|5|9|12|14            1|1|1|1|2         4      R1    R1 L1 I1   R1 L2 I1   R1 L3 I1   R1 L4 I2   NULL       NULL       NULL
11   - - - R1 L3 I2           2       1|5|9|11               1|1|1|2           3      R1    R1 L1 I1   R1 L2 I1   R1 L3 I2   NULL       NULL       NULL       NULL
8    - - R1 L2 I2             2       1|5|8                  1|1|2             2      R1    R1 L1 I1   R1 L2 I2   NULL       NULL       NULL       NULL       NULL
4    - R1 L1 I2               2       1|4                    1|2               1      R1    R1 L1 I2   NULL       NULL       NULL       NULL       NULL       NULL
2    R2                       2       2                      2                 0      R2    NULL       NULL       NULL       NULL       NULL       NULL       NULL
7    - R2 L1 I1               1       2|7                    2|1               1      R2    R2 L1 I1   NULL       NULL       NULL       NULL       NULL       NULL
10   - - R2 L2 I1             1       2|7|10                 2|1|1             2      R2    R2 L1 I1   R2 L2 I1   NULL       NULL       NULL       NULL       NULL
13   - - - R2 L3 I1           1       2|7|10|13              2|1|1|1           3      R2    R2 L1 I1   R2 L2 I1   R2 L3 I1   NULL       NULL       NULL       NULL
6    - R2 L1 I2               2       2|6                    2|2               1      R2    R2 L1 I2   NULL       NULL       NULL       NULL       NULL       NULL
3    R3                       3       3                      3                 0      R3    NULL       NULL       NULL       NULL       NULL       NULL       NULL

Dashes are added in the final statement to show depth of each level of the hierarchy, but typically aren't included.

Notice that the results displayed in proper hierarchical order. The results also sort every level of the hierarchy based off the sort order specified in the original data set. If the result set of your recursive hierarchy is being used for display purposes, consider using one of the sorting options I outlined in this article.

Data Analytics

 

Love our Blogs?

Sign up to get notified of new Skyline posts.

 


Related Content


Blog Article
Power BI’s Latest Features and How to Use Them
Marcus RadueMarcus Radue  |  
Apr 14, 2020
[Updated 05/11/20]  In this regularly updated blog, Marcus Radue (Data Analytics Engineer) highlights key features from his monthly Power BI Office Hours webinar series so you know how to capitalize on Power BI’s latest enhancements.   Power BI Features (April 2020 Update)
Blog Article
What is Microsoft’s Power Platform and How to Use It: The Guide
Skyline Technologies  |  
Jan 14, 2020
In this guide, Libby Fisette (Director of Skyline Modern Workplace team) and Marcus Radue (Data Analytics Engineer), dig into the functionality of the Microsoft Power Platform and how you can leverage this toolset to solve many business situations. From basics to key questions, you will find...
Blog Article
Realtime and Near-Realtime Data Sources and Data Integrity
Matt PlusterMatt Pluster  |  
Dec 17, 2019
In this blog series, Matt Pluster, Director of Skyline’s Data Analytics Consulting Practice, explores data sources and processing options. For a full overview on this topic, check out the Realtime vs Near-Realtime webinar.   In previous blogs in this series, I dug into advantages...
Blog Article
Mitigating the Risks of Realtime or Near-Realtime Data Processing
Matt PlusterMatt Pluster  |  
Dec 10, 2019
In this blog series, Matt Pluster, Director of Skyline’s Data Analytics Consulting Practice, explores data sources and processing options. For a full overview on this topic, check out the Realtime vs Near-Realtime webinar.   In previous blogs in this series, I’ve talked about...
Blog Article
“The Other Realtime”: Low-Latency Data Processing via DirectQuery
Matt PlusterMatt Pluster  |  
Dec 03, 2019
In this blog series, Matt Pluster, Director of Skyline’s Data Analytics Consulting Practice, explores data sources and processing options. For a full overview on this topic, check out the Realtime vs Near-Realtime webinar.    So far in this blog series, we have talked...