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

Answer 2

Given that you're already using hierarchyid, you have everything you need to work your way back from a given category all the way up the hierarchy. First, a table-valued function to break apart a given hierarchyid into all of its constituent parts:

create or alter function decomposeHierarchy(@h hierarchyid)
returns table
as
return
    select h = @h.GetAncestor(n-1)
    from dbo.Numbers as n
    where n.n <= @h.GetLevel()+1;
go

Where dbo.Numbers is just a tally table starting at 1. Note - the ±1 in the TVF definition is a consequence of my tally table starting at 1 while GetLevel is 0-based. Also note - you could probably get away with using GENERATE_SERIES instead of a tally table if you so desire. I've also gone so far as to write a small CLR function to do this decomposition in the past.

Once we have a means by which to decompose the hierarchy, it's a simple matter joining the constituent hierarchyids back to the source data and concat-ing them together.

declare @t table (
    CategoryID int,
    [Node] hierarchyid,
    CategoryName varchar(100)
);

insert into @t 
    (CategoryID, [Node], CategoryName)
values 
    (1, 0x,'Products'),
    (2, 0x58,'Main Category 1'),
    (3, 0x68,'Main Category 2'),
    (4, 0x5AC0,'Subcategory 1A'),
    (5, 0x5B40,'Subcategory 1B');

declare @CategoryID int = 5;
declare @h hierarchyid = (
    select [Node]
    from @t
    where CategoryID = @CategoryID
);
select STRING_AGG(CategoryName, ' > ') within group (order by c.[Node].GetLevel())
from dbo.decomposeHierarchy(@h) as d
join @t as c
    on d.h = c.[Node];