Get hierarchical data in order 2


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
 Product_Master table

Product_Master table

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:

Hierarchical data in order

Hierarchical data in order

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.

Rate This
[Total: 1    Average: 1/5]

Gopal Krishna Ranjan

About Gopal Krishna Ranjan

Gopal has 8 years of industry experience in Software development. He has a head down experience in Data Science, Database, Data Warehouse, Big Data and cloud technologies and has implemented end to end solutions. He has extensively worked on SQL Server, Python, Hadoop, Hive, Spark, Azure, Machine Learning, and MSBI (SSAS, SSIS, and SSRS). He also has good experience in windows and web application development using ASP.Net and C#.


Leave a comment

Your email address will not be published. Required fields are marked *

2 thoughts on “Get hierarchical data in order

  • Carsten Saastamoinen

    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)

    • Gopal Krishna Ranjan
      Gopal Krishna Ranjan Post author

      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.