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
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?  ...
Blog Article
Flattening the 7-layer Recursive Hierarchy Salad
Bob CharapataBob Charapata  |  
Apr 02, 2019
Sometimes organizations must model a hierarchy with data, but they don’t know how deep it will be. Developers often create recursive hierarchy tables for transaction processing systems to solve this problem. Those tables have one column on the table that refers to the table's identity...
Blog Article
How to Use Power BI’s New AI Visual: Key Influencers
Marcus RadueMarcus Radue  |  
Mar 28, 2019
Microsoft has recently released a new Key Influencers visual in their February 2019 release of Power BI. This visual is part of Microsoft’s roadmap to continue to advance the Artificial Intelligence (AI) integration and features within Power BI. Microsoft has already introduced other AI...
Blog Article
Useful Tips for Power BI Desktop
Scott HietpasScott Hietpas  |  
Mar 19, 2019
There are several things within the Power BI desktop resources that I want to point out. One of the best things that happened in a few releases back is the help menu in the Desktop app. Within the help menu is access to Microsoft documentation and videos. Power BI’s blog speaks about this...
Blog Article
How to Retrieve Data from Excel Files and Other Data Sources in Power BI
Scott HietpasScott Hietpas  |  
Mar 05, 2019
Working with OneDrive for business is great for keeping copies of Power BI desktop reports. It can also be a good place for you to store any one-off data or backup versions of data sources in case the files break at some point.   Another reason to save Excel files in OneDrive is so Power...