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 1.1.1.1', 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.
Anchor part
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
Recursive part
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
Break condition
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.
The solution is not correct. If we change the value og CategoriId 2 to 112, the result will not be in the right order. The problem is, that a number is changed to a string.
DECLARE @Product_Master TABLE
(
CategoryID INT PRIMARY KEY,
CategoryName VARCHAR(100),
ParentCategoryID INT
)
INSERT INTO @Product_Master (CategoryID, CategoryName, ParentCategoryID)
VALUES(1, ‘Product 1’, NULL),
(112, ‘Product 112’, 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 1.1.1.1’, 7),
(11, ‘Product 1.3.1’, 6),
(12, ‘Product 112.1’, 112),
(13, ‘Product 112.1.1’, 12),
(14, ‘Product 3.1’, 3),
(15, ‘Product 3.1.1’, 14);
— wrong solution
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);
— correct solution
WITH CTE AS
(
SELECT CAST(RIGHT(‘000000000’ + CAST(CategoryID AS VARCHAR(10)), 10) 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 + ‘.’ + RIGHT(‘000000000’ + CAST(B.CategoryID AS VARCHAR(10)), 10) 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)
Hi Carsten,
Thanks for your valuable inputs.
What I have done in my blog is to get only the hierarchical data in order, and not the overall order. That is if a child (1.1) belongs to parent (1) and there is another child (1.1.1) of 1.1. Then I need to tie this hierarchy with its associated parent.
Now what you are mentioning is the overall ordering. I totally agree with the solution you have given regarding the same but in your scenario you can order only by category. However if end user may want to order by any particular column which could be a single column or n number of columns, in my opinion, above solution may not work.
So if you want to order by any column (single or multiple), what I suggest is to take those columns in anchor part of the CTE and refer the same column name(s) in recursive part of the CTE. Finally just order your data with these columns lastly by the RowId column created already in this article.
*********************For example in your case to sort overall data by category id column only;
;WITH CTE AS
(
SELECT CategoryID AS ColToSort, 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 ColToSort, 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 ColToSort, RowID
OPTION (MAXRECURSION 32767)
**************************In case you want to get overall ordering with multiple columns:
;WITH CTE AS
(
SELECT CategoryName AS ColToSort, CategoryID as ColToSort1, 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 ColToSort, ColToSort1, 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 ColToSort1, ColToSort, RowID
OPTION (MAXRECURSION 32767)
Thanks and keep posting your valuable inputs.