4.11 Aggregating Hierarchies Revised

4.11.1 Problem

You want to perform aggregation on your hierarchy. As before, you wish to sum the cost of a project or task by beginning from a specific vertex and working your way through all levels of the hierarchy. This time, though, you wish to enhance the efficiency of your aggregation by using the ProjectPaths service table created in the previous recipe. In addition to summarizing the cost, you also wish to list the hierarchy in an indented format.

4.11.2 Solution

Recall that the previous aggregation procedure was fairly complex and made use of a temporary table named #stack. With the ProjectsPaths table, that same aggregation process becomes simple enough that you can perform it with the SQL query shown in the following example:

SELECT SUM(cost) Total 
FROM ProjectPaths a JOIN Projects p 
   ON a.VertexId=p.VertexId 
WHERE
   Path LIKE (SELECT Path FROM ProjectPaths WHERE VertexId=1)+'%'

Total       
----------- 
231

The query in this example summarizes the costs of all projects and tasks under VertexId 1. As you can see, the result of 231 was obtained without the need for recursion and without the need for a temporary stack table. It was obtained with only a four-line SELECT statement, as opposed to the 48 lines of procedural code required for the earlier version of the aggregation solution.

You can also make use of the ProjectPaths table to list the project in a hierarchical manner:

SELECT Space(Depth*2)+Name Project
FROM ProjectPaths a JOIN Projects p 
ON a.VertexId=p.VertexId 
WHERE
   Path LIKE (SELECT Path FROM ProjectPaths WHERE VertexId=1)+'%'
ORDER BY a.Path

Project
--------------------------------- 
  New SW
    Development
      UI Implementation
      Coding
      Initial testing
    Beta testing
      Final adjustments
    Production testing
    Specifications
      Interviews
      Drafts
      Consolidations
      Final document
        Presentation
    Prototype
      Calculations
        Correctness Testing
      Database
      UI Design

Again, the ProjectPaths table enabled the desired result to be generated using only a short SQL query, as opposed to the rather long procedure that would otherwise be required. Please note that the order in which tasks are listed in the result might not be the same as you get with the TraverseProjectsRecursive procedure. However, the hierarchical structure of the information is still preserved.

4.11.3 Discussion

The first query joins the ProjectPaths and Projects tables. This is a one-to-one join, since both tables have an equal number of rows. The secret to the query lies in the second part of the WHERE clause:

Path LIKE (SELECT Path FROM ProjectPaths WHERE VertexId=1)+'%'

The WHERE clause gathers all vertices for which the beginning of the path string is equal to the root vertex (in our case, it is .1.). The summation of all costs is then just a simple matter of applying the SUM function to those rows.

Multilevel operations can now be performed efficiently using the ProjectPaths table. Once you know the path to the parent node, you know the paths to all of that node's children. Had you wished to summarize the cost for the Specifications subproject, you could modify the second part of the WHERE clause as follows:

Path LIKE (SELECT Path FROM ProjectPaths WHERE VertexId=2)+'%'

When writing queries using a table like the ProjectPaths table, you need to remember two rules. First, if you wish to perform an operation on a parent vertex together with all its children, you should use the % pattern match operator at the end of the search string in your LIKE predicate. Second, if you wish to exclude the parent from the result set, you should use the _% pattern. The additional underscore in the pattern match string requires that a character be present. Thus, if the parent's path is .1., it will not match a pattern of .1._%. Any children, however, will have a character following the second dot, so they will match the pattern.

The Depth column in the ProjectPaths table allows you to zero in easily on vertices of a given depth. For example, the following query will return a list of all level two projects in the Projects table:

SELECT SUM(cost) Total 
FROM ProjectPaths a JOIN Projects p 
   ON a.VertexId=p.VertexId
WHERE a.Depth=2

The Depth column can also be used to compute indention, as you saw earlier in this recipe's second query:

SELECT Space(Depth*2)+Name Project

In this case, the Depth column value was multiplied by two to determine the correct number of leading spaces for each line of output.