Display full Product Category Path using HierarchyID and Recursive CTE in SQL Server

Question

I have only found this using a classic ParentID foreign key method. I wonder how to display full hierarchy path using the HierarchyID data type in SQL Server 2019.

I have this setup (simplified for this example):

CREATE TABLE tbl2ProductCategories (
    -- Primary Key Field
    ProductCategoryID INT NOT NULL IDENTITY(1,1),
    -- Non Key Fields
    Node HIERARCHYID NOT NULL UNIQUE,
    ProductCategory NVARCHAR(50) NOT NULL,
    -- Constraints
    CONSTRAINT PK_tbl2ProductCategories PRIMARY KEY (ProductCategoryID)
);

CREATE TABLE tbl1Products (
    -- Primary Key Field
    ProductID INT NOT NULL IDENTITY(1,1),
    -- Non Key Fields
    ProductName NVARCHAR(50) NOT NULL UNIQUE,
    -- Constraints
    CONSTRAINT PK_tbl1Products PRIMARY KEY (ProductID),
);

-- Each product can be in multiple categories
CREATE TABLE tbl3ProductsCategories (
    -- Primary Key Field
    ProductID INT NOT NULL,
    ProductCategoryID INT NOT NULL,
    -- Non Key Fields
    IsPrimaryCategory BIT NOT NULL DEFAULT 1
    -- Constraints
    CONSTRAINT PK_tbl3ProductsCategories PRIMARY KEY (ProductID, ProductCategoryID),
    CONSTRAINT FK_tbl3ProductsCategories_tbl1Products FOREIGN KEY (ProductID) REFERENCES tbl1Products (ProductID),
    CONSTRAINT FK_tbl3ProductsCategories_tbl2ProductCategories FOREIGN KEY (ProductCategoryID) REFERENCES tbl2ProductCategories (ProductCategoryID)
);

Now for each product, I want to display its full category path where the category is marked as primary. For example:

Product 1: Products > Category1 > Subcategory1 > Subsubcategory1
Product 2: Products > Category1 > Subcategory3
Product 3: Products > Category5

I tried to write the recursive CTE myself but I got lost. I got stuck at the point below:

WITH category_hierarchy(ProductID, ProductName, FullPath, Level) AS
    (
    SELECT ProductID, ProductName, 0 AS Level, 'Produkty' AS FullPath FROM tbl1Products
    UNION ALL
    SELECT ProductID, ProductName, Level+1 AS Level, FullPath FROM category_hierarchy WHERE 
    )
SELECT * FROM category_hierarchy;

Can you please help me point me in the right direction?

score 0 · Answer 1 · answered Sep 17 '22 at 02:07

select 
concat(ProductName, ': ', 'Products > ', string_agg(ProductCategory, ' > ') within group (order by Node.GetLevel() ASC))
from tbl3ProductsCategories as tbl3
join tbl2ProductCategories as tbl2
  on tbl2.ProductCategoryID = tbl3.ProductCategoryID
join tbl1Products as tbl1
  on tbl1.ProductID = tbl3.ProductID
group by tbl1.ProductID, ProductName

Use string_agg can realize this case, and can be ordered by Node.GetLevel().

I use these statements to insert data. Or you can see DB Fiddle for details.

insert tbl2ProductCategories
output inserted.*
values
('/1/', 'Category1')
, ('/5/', 'Category5')
, ('/1/1/', 'Subcategory1')
, ('/1/3/', 'Subcategory3')
, ('/1/1/1/', 'Subsubcategory1')

insert tbl1Products
output inserted.*
values
('Product 1')
, ('Product 2')
, ('Product 3')

insert tbl3ProductsCategories
output inserted.*
values
(1, 1, 1)
, (2, 1, 1)
, (3, 2, 1) 
, (1, 3, 0)
, (1, 5, 0)
, (2, 4, 0)

Display full Product Category Path using HierarchyID and Recursive CTE in SQL Server

1 Answers1