Scenario 1:
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)) [NodeLevelsPath]
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 ;
----------------------------------------------------------------
Scenario 2:
In the following scenario, we consider the Numbers instead of Text for the Node levels. In this example, the base Parent node starts with 1 as a root Child ID and its Parent is NULL.
/* DROP TABLE [dbo].[tbl_Hierarchy] */
CREATE TABLE [dbo].[tbl_Hierarchy]
(
[RowID] [int] IDENTITY(1,1) NOT NULL,
[ParentID] [varchar](5) NULL,
[ChildID] [varchar](5) NOT NULL
) ON [PRIMARY]
GO
/* TRUNCATE TABLE [dbo].[tbl_Hierarchy] */
INSERT INTO [dbo].[tbl_Hierarchy]
SELECT NULL, 1 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 1, 3 UNION ALL
SELECT 2, 4 UNION ALL
SELECT 3, 5 UNION ALL
SELECT 2, 6
/* SELECT * FROM [dbo].[tbl_Hierarchy]; */
Now, we will generate the Parent-Child Node Levels Path using the Recursive CTE :
;WITH CTE_Hierarchy
AS (
AS (
-- Base case: Select the top-level parent
SELECT
RowID,
ParentID,
ChildID,
CAST(ChildID AS VARCHAR(255)) AS NodeLevelsPath,
ChildID AS RootChildID
FROM [dbo].[tbl_Hierarchy]
WHERE ParentID IS NULL -- Start with the top parent
UNION ALL
-- Recursive case: Join child to parent and build the path
SELECT
t.RowID,
t.ParentID,
t.ChildID,
CAST(c.NodeLevelsPath + '|' + t.ChildID AS VARCHAR(255)) AS NodeLevelsPath,
c.RootChildID
FROM [dbo].[tbl_Hierarchy] t
INNER JOIN CTE_Hierarchy c
ON c.ChildID = t.ParentID
)
-- Final select: Return the desired paths including the top-level parent
SELECT RowID, ParentID, ChildID, NodeLevelsPath
FROM CTE_Hierarchy
ORDER BY NodeLevelsPath;
--Result:
--Result:
----------------------------------------------------------------
Scenario 3:
In the below scenario, the root Parent node starts with 1 as a base, and Its Parent information is not available.
Scenario 3:
In the below scenario, the root Parent node starts with 1 as a base, and Its Parent information is not available.
/* DROP TABLE [dbo].[tbl_Hierarchy] */
CREATE TABLE [dbo].[tbl_Hierarchy]
(
[RowID] [int] IDENTITY(1,1) NOT NULL,
[ParentID] [varchar](5) NULL,
[ChildID] [varchar](5) NOT NULL
) ON [PRIMARY]
GO
/* TRUNCATE TABLE [dbo].[tbl_Hierarchy] */
INSERT INTO [dbo].[tbl_Hierarchy]
SELECT 1, 2 UNION ALL
SELECT 1, 3 UNION ALL
SELECT 2, 4 UNION ALL
SELECT 3, 5 UNION ALL
SELECT 2, 6
/* SELECT * FROM [dbo].[tbl_Hierarchy]; */
Now, we will generate the Parent-Child Node Levels Path using the Recursive CTE :
;WITH CTE_Hierarchy
AS (
-- Base case: Select initial parent-child relationship with top-level parents
SELECT
RowID, ParentID, ChildID,
CAST(ParentID + '|' + ChildID AS VARCHAR(255)) AS NodeLevelsPath,
ParentID AS RootParentID
FROM [dbo].[tbl_Hierarchy]
WHERE ParentID = '1' -- Start with the top parent
UNION ALL
-- Recursive case: Join child to parent and build the path
SELECT
t.RowID, t.ParentID, t.ChildID,
CAST(c.NodeLevelsPath + '|' + t.ChildID AS VARCHAR(255)) AS NodeLevelsPath,
c.RootParentID
FROM [dbo].[tbl_Hierarchy] t
INNER JOIN CTE_Hierarchy c
ON c.ChildID = t.ParentID
)
-- Final select: Return only the desired paths
SELECT RowID, ParentID, ChildID, NodeLevelsPath
FROM CTE_Hierarchy
WHERE RootParentID = '1'
ORDER BY NodeLevelsPath;
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.