I create a web application and I need to show the navigation to the current page as breadcrumb. The structure of the menu is stored in the database and I will use a recursive CTE to show the current page as breadcrumb.

I store the menu in the database, because it is dynamic, i.e. i use a web interface to control the menu items and I show/hide menu items to a customer, based ot their permissions.

The current menu has 3 levels and its structure is:

Customers
	Add
	Contact
		Send Email
		Create Package
	Reports
		Birthdays
		Sales

Items
	Add
	Manage Inventory
		Store 1
		Store 2

Sale
	Add
	Reports
		By Period
		By Customer
		By Item

USE PL;
GO

DROP TABLE IF EXISTS #Menu;

CREATE TABLE #Menu
	(
		PageID INT
		, ParentPageID INT
		, [Name] NVARCHAR(128)
	);

INSERT #Menu (ParentPageID, PageID, [Name])
SELECT						NULL, 1, N'Customers'
UNION ALL SELECT	NULL, 2, N'Items'
UNION ALL SELECT	NULL, 3, N'Sales'
UNION ALL SELECT	1, 4, N'Add'
UNION ALL SELECT	1, 5, N'Contact'
UNION ALL SELECT	1, 6, N'Reports'
UNION ALL SELECT	2, 7, N'Add'
UNION ALL SELECT	2, 8, N'Manage Inventory'
UNION ALL SELECT	3, 9, N'Add'
UNION ALL SELECT	3, 10, N'Reports'
UNION ALL SELECT 5, 11, N'Send Email'
UNION ALL SELECT 5, 12, N'Create Package'
UNION ALL SELECT 6, 13, N'Birthdays'
UNION ALL SELECT 6, 14, N'Sales'
UNION ALL SELECT 8, 15, N'Store 1'
UNION ALL SELECT 8, 16, N'Store 2'
UNION ALL SELECT 10, 17, N'By Period'
UNION ALL SELECT 10, 18, N'By Customer'
UNION ALL SELECT 10, 19, N'By Item';

SELECT *
FROM #Menu;
GO

I use a Parent – Child hierarchy to create a multi-level menu.

The menu with the breadcrumbs:

WITH CTE_Page AS -- Parent to child
	(
		SELECT
			P1.ParentPageID
			, P1.PageID
			, 0 AS [Level]
			, P1.[Name]
			, CAST(P1.[Name] AS NVARCHAR(MAX)) AS Breadcrumb
		FROM #Menu AS P1
		WHERE P1.ParentPageID IS NULL
		
		UNION ALL

		SELECT
			P2.ParentPageID
			, P2.PageID
			, C.[Level] + 1
			, P2.[Name]
			, CONCAT(CAST(C.Breadcrumb AS NVARCHAR(MAX)), N' > ', CAST(P2.[Name] AS NVARCHAR(MAX)))
		FROM
			#Menu AS P2
			JOIN CTE_Page AS C
				ON P2.ParentPageID = C.PageID
	)
SELECT *
FROM CTE_Page
ORDER BY ParentPageID
GO

The above query is parent to child recursive CTE. I need to show the breadcrumb for a specified PageID, i.e. I need to build the breadcrumb as child to parent.

To translate PageID 17 to breadcrumb, I wrote recursive CTE with child to parent logic:

DECLARE @PageID INT = 17;

WITH CTE_Menu AS -- Child to Parent
(
	SELECT
		P1.ParentPageID
		, P1.PageID
		, CAST([Name] AS NVARCHAR(MAX)) AS Breadcrumb
	FROM #Menu AS P1
	WHERE PageID = @PageID

	UNION ALL
	
	SELECT
		P2.ParentPageID
		, C.PageID
		, CONCAT(CAST(P2.[Name] AS NVARCHAR(MAX)), N' > ', CAST(C.Breadcrumb AS NVARCHAR(MAX)))
	FROM
		#Menu AS P2
		JOIN CTE_Menu AS C
			ON C.ParentPageID = P2.PageID
)
SELECT Breadcrumb
FROM CTE_Menu
WHERE ParentPageID IS NULL;

The Parent-Child CTE relations are explained in Learn Microsoft® SQL Server® Intuitively (page 216).

Keep it simple :-)