SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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)
);
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)


ProductCategoryID
Node
ProductCategory


1
/1/
Category1

2
/5/
Category5

3
/1/1/
Subcategory1

4
/1/3/
Subcategory3

5
/1/1/1/
Subsubcategory1




ProductID
ProductName


1
Product 1

2
Product 2

3
Product 3




ProductID
ProductCategoryID
IsPrimaryCategory


1
1
True

2
1
True

3
2
True

1
3
False

1
5
False

2
4
False


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


(No column name)


Product 1: Products > Category1 > Subcategory1 > Subsubcategory1

Product 2: Products > Category1 > Subcategory3

Product 3: Products > Category5
ProductCategoryID	Node	ProductCategory
1	/1/	Category1
2	/5/	Category5
3	/1/1/	Subcategory1
4	/1/3/	Subcategory3
5	/1/1/1/	Subsubcategory1