I have received a number of requests for an explanation of my winning query of TSQL Challenge 19. This involved traversing a hierarchy of employees and rolling a count of orders from subordinates up to superiors.
The first concept I shall address is the hierarchyId , which is constructed within the CTE called cteTree. cteTree is a recursive cte that will expand the parent-child hierarchy of the personnel in the table @emp. One useful feature with a recursive cte is that data can be ‘passed’ from the parent to the child data. The hierarchyId column is similar to the hierarchyId data type that was introduced in SQL Server 2008 and represents the position of the person within the organisation.