T-SQL: Recursive CTE. List Child to Parent (Create Breadcrumb)


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:

Let’s simulate the table in which the menu is stored:

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

The menu with the breadcrumbs:

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:

Keep it simple :-)