Product Category Hierarchy (with Breadcrumb Trail) using CTE query

The example query below creates a product category table and populates it with sample data. I then use a CTE query to return the categories, along with the breadcrumb trail (lineage)

Results:

ID Category ParentID Level Lineage
1 All Products NULL 0 All Products
2 Cars 1 1 All Products -> Cars
11 Motorbikes 1 1 All Products -> Motorbikes
10 BMW 2 2 All Products -> Cars -> BMW
6 Ford 2 2 All Products -> Cars -> Ford
3 Vauxhall 2 2 All Products -> Cars -> Vauxhall
5 Astra 3 3 All Products -> Cars -> Vauxhall -> Astra
4 Corsa 3 3 All Products -> Cars -> Vauxhall -> Corsa
7 Fiesta 6 3 All Products -> Cars -> Ford -> Fiesta
8 Ka 6 3 All Products -> Cars -> Ford -> Ka
9 Mondea 6 3 All Products -> Cars -> Ford -> Mondea
12 Kawasaki 11 2 All Products -> Motorbikes -> Kawasaki
13 Suzuki 11 2 All Products -> Motorbikes -> Suzuki
Updated: Tuesday, 1st May, 2018 — 2:20 pm

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

CJayMead's SQL Server, Endeca, MS Office Help © 2018