In this post, we are going to learn how we can get hierarchical data in order. We are dealing with a user defined hierarchical data which can have “n” number of siblings and their siblings can also have “n” number of siblings in turn.
For demo purpose, we have a Product_Master table, in which a product can be marked as a parent of another product, and that child product can also be marked as a parent of another product, such kind of hierarchy is known as user defined hierarchy. As any product can be marked as another product’s parent, we can have any number of nested products in the hierarchy. We have to retrieve all these products with their associated parent in ordered way.
Ordered hierarchical data from above user defined hierarchy, can be achieved with a recursive common table expression (CTE) or using a loop. We are using recursive CTE to achieve this.
Below is our product table:
DECLARE @Product_Master TABLE ( CategoryID INT PRIMARY KEY, CategoryName VARCHAR(100), ParentCategoryID INT )
Lets put some dummy data in this table now:
INSERT INTO @Product_Master (CategoryID, CategoryName, ParentCategoryID) VALUES(1, 'Product 1', NULL), (2, 'Product 2', NULL), (3, 'Product 3', NULL), (4, 'Product 1.1', 1), (5, 'Product 1.2', 1), (6, 'Product 1.3', 1), (7, 'Product 1.1.1', 4), (8, 'Product 1.1.2', 4), (9, 'Product 1.1.3', 4), (10, 'Product 126.96.36.199', 7), (11, 'Product 1.3.1', 6), (12, 'Product 2.1', 2), (13, 'Product 2.1.1', 12), (14, 'Product 3.1', 3), (15, 'Product 3.1.1', 14)
Have a look on the product table’s columns and data:
SELECT * FROM @Product_Master
In above image, we can see that Product_Master table has a ParentcategoryID column which is used to mark parent for the line item product. ParentcategoryID is NULL for the products which are on top of the hierarchy that means they don’t have a parent product in chain.
Query to get hierarchical data in order
Below is the recursive CTE query to retrieve the hierarchical data in ordered way:
WITH CTE AS ( SELECT CAST(CategoryID AS VARCHAR(100)) AS RowID, --to order the hierarchical data 'Level ' + CAST(1 AS VARCHAR(10)) AS ProductLevel, 1 AS HierarchySeq, --to create a level of hierarchical data CategoryID, CategoryName, ParentCategoryID, CAST('' AS VARCHAR(100)) AS ParentcategoryName FROM @Product_Master WHERE ParentCategoryID IS NULL UNION ALL SELECT CAST(RowID + '.' + CAST(B.CategoryID AS VARCHAR(100)) AS VARCHAR(100)), 'Level ' + (CAST (HierarchySeq + 1 AS VARCHAR(10))) AS ProductLevel, HierarchySeq + 1 AS HierarchySeq, B.CategoryID, B.CategoryName, B.ParentCategoryID, A.CategoryName AS ParentcategoryName FROM CTE A INNER JOIN @Product_Master B ON A.CategoryID = B.ParentCategoryID ) --Get ordered output data from CTE SELECT * FROM CTE ORDER BY RowID OPTION (MAXRECURSION 32767)
And below is the output:
How it works?
As we know that a recursive CTE is made of three parts; Anchor part, Recursive Part and Break condition. Below is the explanation of the logic used in each part of the recursive CTE.
Inside anchor part of the recursive CTE, we have extarcted only master products from the Product_Master table using below filter condition:
WHERE ParentCategoryID IS NULL
We have used a logic to generate a RowID column to be used to sort the hierarchical data finally:
CAST(CategoryID AS VARCHAR(100)) AS RowID
To create a level for the product item in the hierarchy, we have used below code line:
'Level ' + CAST(1 AS VARCHAR(10)) AS SEQ
In the recursive part of CTE, we have put a INNER JOIN between anchor part (which has all master product from Product_Master) and Product_Master table based on CategoryID and ParentCategoryID, to get all siblings from the Product_Master table up to any level in recursive way.
To get the ordered data, we have forged the RowID by concatenating its parent category id with the line item product item as below:
CAST(RowID + '.' + CAST(B.CategoryID AS VARCHAR(100)) AS VARCHAR(100)) AS RowID
To get the level detail of the product, we have used below code line:
'Level ' + (CAST (ProductLevel + 1 AS VARCHAR(10))) AS SEQ
Inside break condition, we have used a inner join with below condition to break the recursion when no more product found in the hierarchy chain:
ON A.CategoryID = B.ParentCategoryID
Thank you for reading, and please do share and rate this post if you really liked it. I would also request you to put your valuable feedback in comment section of this post.