1. Working with recursive queries
We have seen how to create a recursive CTE, or common table expression. Now, we will focus on solving tasks with them.
2. The hierarchy of an IT-organization
A common use for a recursive CTE is to query hierarchical data, such as organizational charts or a bill of materials. We will use the data of an IT-organization. It is contains the ID of the employee, the name of the employee, the job title of the employee in the company, the department in the company and the ID of the supervisor. It is important to understand that the ID describes the ID of an employee and supervisor represents the ID of the supervisor. An example could be that the employee with ID two has supervisor one, which means that the employee with ID one is the supervisor of employee with ID two. Therefore, by having these two fields, we know who the supervisor is or if someone is the supervisor.
3. The IT-organization
Here is what the data looks like. We can see for example that Hannes Berg is the Operation Manager and is supervised by the employee with the ID one which is Heinz Grießer.
4. Common tasks for hierarchical data
Let's look at how to get the hierarchy of a record. Our first task is to find out who the supervisor is for a specific employee.
The second task is to get the position of an employee in the hierarchy, meaning their position in the organization.
The final task is to combine the recursion result into one field. This could be, for example, to combine all supervisors of an employee in the hierarchy.
5. Get the hierarchy
Let's get the hierarchy from the table called employee. As mentioned, our first task is to find the supervisor for a specific employee.
We'll do this by defining the anchor member filtering employee with the supervisor equals zero in the where clause.
The recursive member will find the employee in the hierarchy until no more recursion steps are possible because no more data is available in the table.
We need to use a join to combine queries on the ID of the employee and the ID of the supervisor.
6. Get the hierarchy
A possible result can be seen here. Here the employee with ID one has no supervisor, the employee with ID two is supervised by employee one and employee three has employee two as supervisor.
7. Get the level of the hierarchy
The next task is to get the level of a hierarchy. Therefore, we have to introduce a new field called level in the anchor member. The level has to be increased with every recursion step and we have to join the new CTE with the employee table to get the wanted fields.
8. Get the level of the hierarchy
A possible result could look like this. Employee one is on the top level zero and employee two is on the next level one.
9. Combine recursion results into one field
The last task is to combine the recursion results into one field.
For this, we have to use the SQL statement CAST which is used to convert a data type. The task is now to track all supervisors in the hierarchy for one employee.
The described task could be done by CASTing the ID of data type number to the new field called PATH of type char in the anchor member. In the recursion step, we add the next ID to the PATH for each recursion step. We have to cast the information from a number to a string to be consistent with the path.
10. Combine recursion results into one field
Here is an example result ranking the employees. The highest rank is the starting ID, which in this case is zero.
11. Let's query the IT-organization
Now it is your turn to practice these principles!