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
Data Lake vs Data Warehouse: Avoiding the Data Swamp
Scott HietpasScott Hietpas  |  
Jul 16, 2019
In this blog series, Scott Hietpas, a principal consultant with Skyline Technologies’ data team, and Matt Pluster, the Data Analytics and Data Platform director, respond to some common questions on data warehouses and data lakes. Previous installments covered pros and cons of each solution,...
Blog Article
Data Lake vs Data Warehouse: What’s the Best Configuration?
Scott HietpasScott Hietpas  |  
Jul 02, 2019
In this blog series, Scott Hietpas, a principal consultant with Skyline Technologies’ data team, responds to some common questions on data warehouses and data lakes. For a full overview on this topic, check out the original Data Lake vs Data Warehouse webinar.   In my previous blog...
Blog Article
Data Lake vs Data Warehouse: Pros and Cons
Scott HietpasScott Hietpas  |  
Jun 18, 2019
In this blog series, Scott Hietpas, a principal consultant with Skyline Technologies’ data team, responds to some common questions on data warehouses and data lakes. For a full overview on this topic, check out the original Data Lake vs Data Warehouse webinar.   There's a lot of...
Blog Article
Machine Monitoring IoT Solution with Azure Services and Power BI
Eric SaltzmannEric Saltzmann  |  
Jun 11, 2019
We often hear organizations ask how they can drive more insights out of their connected devices. Though the Internet of Things (IoT) has been a buzzword for the last few years, many organizations are still struggling through the headache of implementing an IoT pilot or solution. Most of the...
Blog Article
10 KPIs Manufacturers Should Track for Operational Excellence
Paul FullerPaul Fuller  |  
Apr 18, 2019
How do you know if you’re truly improving quality and efficiency in your manufacturing operations? Do you know if your equipment is as effective as you think it is? Are your operating lines a bottleneck in getting orders delivered to your customers? How would you demonstrate that?  ...