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)

CREATE TABLE dbo.ProductCategory
( ID INT NOT NULL PRIMARY KEY,
  Category VARCHAR(100) NOT NULL,
  ParentID INT NULL)
GO

INSERT INTO dbo.ProductCategory (ID, Category, ParentID)
VALUES	(1, 'All Products', NULL),
	(2, 'Cars', 1), (3, 'Vauxhall', 2),
	(4, 'Corsa', 3), (5, 'Astra', 3),
	(6, 'Ford', 2), (7, 'Fiesta', 6),
	(8, 'Ka', 6), (9, 'Mondea', 6),
	(10, 'BMW', 2), (11, 'Motorbikes', 1),
	(12, 'Kawasaki', 11), (13, 'Suzuki', 11)
GO

WITH CTE_ProductCategories (ID, Category, ParentID, Level, Lineage)
AS (
	SELECT ID, Category, ParentID, 0 AS Level, CAST ([Category] AS VARCHAR (MAX)) AS [Lineage]
	FROM dbo.ProductCategory
	WHERE ParentID IS NULL
	UNION ALL
	SELECT pc.ID, pc.Category, pc.ParentID, p1.Level + 1, p1.Lineage + ' -> ' + CAST ([pc].[Category] AS VARCHAR (MAX))
	FROM dbo.ProductCategory AS pc
	JOIN CTE_ProductCategories AS p1 ON p1.ID = pc.ParentID
)
SELECT ID, Category, ParentID, Level, Lineage
FROM CTE_ProductCategories
ORDER BY ParentID, Category
GO

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 e-mail address will not be published. Required fields are marked *

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