By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE YourTable (
"CategoryID" INTEGER PRIMARY KEY,
"Node" hierarchyid,
"CategoryName" VARCHAR(100),
INDEX IX (CategoryName) INCLUDE (Node)
);
INSERT INTO YourTable
("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');
5 rows affected
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;
| CategoryID | Node | CategoryName | BreadCrumb | ParentNode |
|---|---|---|---|---|
| 1 | / | Products | Products | null |
| 5 | /1/2/ | Subcategory 1B | Products -> Main Category 1 -> Subcategory 1B | null |
| 4 | /1/1/ | Subcategory 1A | Products -> Main Category 1 -> Subcategory 1A | null |
| 3 | /2/ | Main Category 2 | Products -> Main Category 2 | null |
| 2 | /1/ | Main Category 1 | Products -> Main Category 1 | null |