Create breadcrumbs navigation using HIERARCHYID?

Question

I have Product Categories table in Microsoft SQL Server:

CategoryID (IDENTITY)	Node (HIERARCHYID)	CategoryName (NVARCHAR(100)
1	0x	Products
2	0x58	Main Category 1
3	0x68	Main Category 2
4	0x5AC0	Subcategory 1A
5	0x5B40	Subcategory 1B

I want to display breadcrumbs for each category, for example for Subcategory 1A:

Products > Main Category 1 > Subcategory 1A

Do I use recursive CTE as if there was a self join (ParentID) instead of HIERARCHYID, or does this work differently with HIERARCHYID? All materials I found on the internet regarding breadcrumbs assume that ParentID is used, not HIERARCHYID.

Can you please provide me an example?

Thank you.

Answer 1

You will always need a recursive CTE, unless you pre-calculate the breadcrumb as your create or update the hierarchy.

You can get the parent ID using .GetAncestor(1) on the Node column.

Filter the CTE so the final result only returns rows which have recursed up to the top.

WITH cte AS (
    SELECT
      t.*,
      BreadCrumb = CAST(t.CategoryName AS nvarchar(max)),
      ParentNode = t.Node.GetAncestor(1)
    FROM YourTable t

    UNION ALL

    SELECT
      cte.CategoryID,
      cte.Node,
      cte.CategoryName,
      CONCAT(t.CategoryName, ' -> ', cte.BreadCrumb),
      t.Node.GetAncestor(1)
    FROM cte
    JOIN YourTable t ON t.Node = cte.ParentNode
)
SELECT *
FROM cte
WHERE cte.ParentNode IS NULL;

db<>fiddle