CREATE TABLE categories (
id int IDENTITY(1,1) not null,
created_date datetime not null DEFAULT GETDATE(),
updated_date datetime not null DEFAULT GETDATE(),
title nvarchar(max) NOT NULL,
CONSTRAINT PK_categories_id PRIMARY KEY CLUSTERED (id),
);
CREATE TABLE childcategories (
parent_id int,
child_id int,
CONSTRAINT PK_childcategories PRIMARY KEY NONCLUSTERED ([parent_id],[child_id]),
CONSTRAINT FK_childcategories_parent_id FOREIGN KEY (parent_id) REFERENCES categories(id),
CONSTRAINT FK_childcategories_child_id FOREIGN KEY (child_id) REFERENCES categories(id),
);
insert into categories(title)
values ('hardware'), ('cpu'), ('gpu') , ('intel'), ('amd'), ('nvidia') , ('geforce')
insert into childcategories(parent_id,child_id)
values (1,2), (1,3),(2,4), (2,5),(3,4), (3,5), (3,6), (6,7)
with cte as (
select c.id,c.title as parent_title, 1 as [level],cat.title as child_title,
case when c.title<>cat.title then c.title + ' > ' + cat.title else c.title end as breadcrumb
from categories as c
left join childcategories as ch on ch.parent_id=c.id
left join categories as cat on cat.id=ch.parent_id
where ch.parent_id is not null
union all
select cat.id,cte.parent_title as parent_title, cte.[level] + 1,cat.title as child_title,
case when cte.parent_title<>cat.title and cte.parent_title<>par.title then cte.parent_title + ' > ' +par.title + ' > ' + cat.title else par.title + ' > ' +cat.title end as breadcrumb
from cte
inner join childcategories as ch on ch.parent_id=cte.id
inner join categories as cat on cat.id=ch.child_id
inner join categories as par on par.id=cte.id
)
select distinct * from cte order by id, [level];