How to create Parent and Child Hierarchy using Recursive CTE in SQL Server

How to create Root Node and Leaf Node Hierarchy using Recursive CTE in SQL Server
Scenario:
Suppose we have a sample data with ParentNode and ChildNode columns as follows.

Lets say:
A1 is the Parent Node for the Child Node B1. This Child Node can be RootNode or a Parent Node for other Child Nodes as per below.
B1 is the Parent Node for {C1, D1}.
C1 is the Parent Node for {E1, F1}.
Please note that, the A1 is the Root Node which do not have the Parent Node assigned.
Based on this sample, we have to create a [NodeLevelsPath] column with values as per below:
The Node Hierarchy for A1 = A1
The Node Hierarchy for C1 = A1|B1|C1
The Node Hierarchy for H1 = A1|B1|C1|F1|H1

We can achieve this Scenario using the Recursive CTE in SQL Server as explained below:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

USE MyAnalyticsDB

GO

/* Check and Drop the table if it already exists: */

IF OBJECT_ID('[dbo].[tbl_Hierarchy]','U') IS NOT NULL

    BEGIN

        DROP  TABLE [dbo].[tbl_Hierarchy]

    END

ELSE

    BEGIN

       PRINT 'The target Object unable to Drop as it is Not Found'

    END

/* Create a Sample Hierarchy Table */

CREATE TABLE [dbo].[tbl_Hierarchy]

    (

        [RowID] [int] IDENTITY(1,1) NOT NULL,

        [ParentNodeId] [varchar](5) NULL,

        [ChildNodeId] [varchar](5) NOT NULL

    ) ON [PRIMARY]

    GO

/* Insert sample data into the Hierarchy table */

INSERT INTO [dbo].[tbl_Hierarchy]

SELECT NULL, 'A1' UNION ALL

SELECT 'A1', 'B1' UNION ALL

SELECT 'B1', 'C1' UNION ALL

SELECT 'B1', 'D1' UNION ALL

SELECT 'C1', 'E1' UNION ALL

SELECT 'C1', 'F1' UNION ALL

SELECT 'E1', 'G1' UNION ALL

SELECT 'F1', 'H1';

Select * From [dbo].[tbl_Hierarchy] ;

/* Create Recursive CTE to define the Hierarchy Node Levels Path */

WITH CTE_Hierarchy AS (

    SELECT

        RowID, ParentNodeId, ChildNodeId,

        CAST(ISNULL(ParentNodeId,'') + ChildNodeId AS VARCHAR(255)) [NodeLevelsPath]

    FROM tbl_Hierarchy

    WHERE ParentNodeId IS NULL

    UNION ALL

    SELECT

        t.RowID, t.ParentNodeId, t.ChildNodeId,

        CAST(c.[NodeLevelsPath] + '|' + t.ChildNodeId AS VARCHAR(255)) [Path]

    FROM tbl_Hierarchy t

    INNER JOIN CTE_Hierarchy c ON c.ChildNodeId = t.ParentNodeId

    )

SELECT * FROM CTE_Hierarchy;

/* To Calculate the Child Node Level */

SELECT *,

        CONCAT('Level ',

            CAST(LEN(cte.[NodeLevelsPath])-LEN(REPLACE(cte.[NodeLevelsPath],'|',''))+1

                        AS VARCHAR(10) )

            )  AS "NodeLevel"

 FROM CTE_Hierarchy cte ;

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------