Even though SQL Server is a relational database and we could be able to model hierarchical data. For this blog we will only be talking about how we could create a level for relations that has a parent-child relationship.
Let us see how we could do that in action:
To demonstrate this example I have a table named [parent child] that hold parent-child relationship of a biblical names.
The simplest way to create the hierarchy level for parent child relationship is by using a CTE recursive queries. If you are not familiar with how to create a recursive query please take a look at my pervious blog post before moving forward by using this link (recursive queries).
;WITH NoChildren AS ( -- Anchor member -- SELECT 0 AS [Level] ,P.ID ,P.ParentID ,P.Name FROM dbo.[Parent Child] AS P WHERE P.ParentID IS NULL UNION ALL -- Recursive member -- SELECT NC.[Level] + 1 ,C.ID ,C.ParentID ,C.Name FROM dbo.[Parent Child] AS C JOIN NoChildren NC ON C.ParentID = NC.ID ) SELECT ID ,ParentID , LEFT(replicate(' ',[Level]) + Name,20) Name --The left function is used to give extra spaces to for the tree levels ,[Level] FROM NoChildren;
Finally if we select “Result to Text” button on the toolbar we will get a result set like below.