Displaying Hierarchical Data

This is the ninth article in a short series of SQL Server query challenges to test and expand your SQL Server querying skills. While none of these short articles is of overwhelming complexity, they will hopefully either teach you something new or help you to reactivate potentially dormant SQL skills in preparation for your next real-world challenge. You can see the rest of the articles on the Query Answers with SQL Server page.

In this article we will once again imagine that you are working for a small classic car retailer called Prestige Cars Ltd, using the data that is in the company database.

The Challenge

The CEO wants a list of staff and their managers that also displays each person’s level in the company structure (with her at the top, obviously). A long time ago you met the Staff table that you can see in Figure 1.

Figure 1. The Staff table

So you decide to use this data to display staff details along with their manager and the level of each manager and staff member in the corporate hierarchy. You use the following code to do this:

;
WITH HierarchyList_CTE
AS
(
SELECT    StaffID, StaffName, Department, ManagerID, 1 AS StaffLevel
FROM      Reference.Staff
WHERE     ManagerID IS NULL
UNION ALL
SELECT         ST.StaffID, ST.StaffName
              ,ST.Department, ST.ManagerID, StaffLevel + 1
FROM          Reference.Staff ST
INNER JOIN    HierarchyList_CTE CTE
              ON ST.ManagerID = CTE.StaffID
)
SELECT         STF.Department
              ,STF.StaffName
              ,CTE.StaffName AS ManagerName
              ,CTE.StaffLevel 
FROM          HierarchyList_CTE CTE
INNER JOIN    Reference.Staff STF
              ON STF.ManagerID = CTE.StaffID

Running this code produces the output that you can see in Figure 2.

Figure 2. Hierarchical output

How it Works

This query shows the levels of the staff hierarchy, despite the fact that these levels are not explicitly given in the source data. It does this by using a technique known as recursion. This means that at some point a process repeats itself and references itself in the loop that is repeated.

In the case of this particular query the core is a piece of code called a recursive CTE. This means that the CTE runs repeatedly starting at the highest level of the staff hierarchy and progressing down through the personnel “pyramid” until it stops when it reaches the bottom.

What the code does is explained indetail below.

Define a root query

This is the starting point for the CTE. This is the first query inside the actual CTE. It defines the starting point for the hierarchy of numbers that define the level of each staff member. You can see the output from this query in Figure 3.

Figure 3. The output from the root query in a recursive CTE

Add a recursive query

This query is independent of the root query. It joins two datasets – the Staff table and the CTE itself. These are joined on the StaffID and the ManagerID so that the reference to each staff member’s manager creates the hierarchy. It then increments the counter each time that a new level is found in the Staff table, thus returning the depth of the staff hierarchy as a number. When a manager has no further levels of staff below then the recursion stops.

To make this clearer, take a look at Figure 4 where the recursive CTE is explained graphically. Specifically you can see that the second query in the CTE refers to the name of the CTE. This means that the second query will be repeated each time that a match exists between the join fields. In other words, for every match between a manager ID and a staff member ID the query will run again.

Figure 4. The concept of a recursive CTE

Finally the output query takes the result from the CTE and joins it yet again to the staff table to obtain the manager of each staff member.

Tricks and Traps

There is one useful point to remember here. One cool use of this query is that it can easily be extended to show only the staff in a particular department if you add a final couple of lines of code like these:

WHERE         STF.Department = 'Finance'
ORDER BY      CTE.StaffLevel

Running the whole query now gives the result for the Finance department that you can see in Figure 5.

Figure 5. Filtering Hierarchical output

That is it – you have seen a simple example of how to create hierarchical output from a Dataset. Keep watching SQL Server Central.com ready for the next article in this series.

This article is adapted from the book “Query Answers with SQL Server Volume II: In-Depth Querying” by Adam and Karine Aspin. Available as both a paperback and an eBook from all good resellers including Amazon, Kobo and iBooks, as well as all good bookstores.

The sample data is available either in the accompanying material for this article, or on the Tetras Publishing website at: www.tetraspublishing.com/sqlserver.

The Series

There are a number of articles in this series. You can see them all on the Query Answers page.

The Challenge