Recursive queries with hierarchical result using SQL Server
Recursive queries are not commonly used, yet they are very useful to display hierarchical data stored in flat tables.
Unlike the “Connect by” statement in Oracle, SQL Server doesn’t provide an easy answer to build them.
Here is a way to solve this with SQL Server, found after a bit of reading.
Lets use a flat table named “Example” containing some hierarchical data with the following column:
| ID | Name | ParentID |
|---|---|---|
| 1 | Root Name | NULL |
| 2 | child1 | 1 |
| 3 | child2 | 1 |
| 4 | Grandchild1 | 2 |
| 5 | Crandchild2 | 2 |
| 6 | Grandchild1 | 3 |
In this table, any row can have a parent (except the root one), and therefore a hierarchy view could be created:
| Root: Level 1 | Level 2 | Level 3 |
|---|---|---|
| Root Name | ||
| Child1 | ||
| Grandchild1 | ||
| Grandchild2 | ||
| Child2 | ||
| Grandchild1 |
In order to create a query to display this hierarchical view, we first create a recursive source (‘recursive_source’) to provide the expected result. We will need the following 4 columns in our result:
- ID: a unique identifier used for joining itself recursively to the parent ID,
- The name: this is what we want to display in our report,
- The depth level: to be able to indent the result nicely
- A sort field: to ensure we display the data in proper hierarchical order
WITH recursive_source( ID, Name, Depth_Level, Sort) AS
(
The first selection is only to get the starting point, the root of our hierarchy (level 1), with the “Parent Name”:
Select root.ID,
convert(varchar(255), root.name), /* The name, we need no discrepancy in the column type so we convert this into varchar */
1, /* depth level: this is the first iteration, therefore we set it at level 1 */
convert(varchar(255), root.name) /* we will start with the parent name for sorting our output */
from Example as root /* we name this first iteration table "root" */
Where root.ID =
This is where we create the recursive query
UNION ALL
Select iteration.ID,
convert(varchar(255), REPLICATE('|---', Depth_Level) + iteration.Name), /* Add "|---" as many time as Depth_Level (= Replicate) in front of the name, this creates a natural indentation of the result */
Depth_Level + 1, /* increment Depth_Level for next iteration */
convert(varchar(255), RTRIM(Sort) + '|---' + iteration.Name) /* Append the new name at the end of the sort field + "|---" to keep the hierarchical order of the data as previous level are in front of the name */
From Example as iteration, recursive_source /* The source is recursively queried */
where recursive_source.ID = iteration.ParentID
)
Then create the select statement which will display the sorted recursive result with hierarchical indentation:
Select Name 'Hierarchical Structure'
from recursive_source
order by Sort
Here is the full query in one block an no comment:
WITH recursive_source( ID, Name, Depth_Level, Sort) AS
(Select root.ID,
convert(varchar(255), root.name),
1,
convert(varchar(255), root.name)
from Example as root
where root.ID = UNION ALL Select iteration.ID, convert(varchar(255), REPLICATE('|---', Depth_Level) + iteration.Name), Depth_Level + 1, convert(varchar(255), RTRIM(Sort) + '|---' + iteration.Name) From Example as iteration, recursive_source where recursive_source.ID = iteration.ParentID ) Select Name 'Hierarchical Structure' from recursive_source order by Sort