ALTER procedure [dbo].[sp_EndecaFeed_GetAll_ClassesHierarchy]
as
--List all hierarchy
with Hierarchy(ClassID, ClassName, FullName, ParentClassID, ParentHierarchy, Seq) as
(
select FullName = ClassID, ClassName, convert(nvarchar(1000), ClassName), ParentClassID, convert(nvarchar(1000), ClassName) as ParentHierarchy, Seq = 1
from Classes
union all
select FullName = p.ClassID, p.ClassName, convert(nvarchar(1000),FullName + '/' + p.ClassName), p.ParentClassID, convert(nvarchar(1000), FullName) as ParentHierarchy, Seq = Seq + 1
from Classes p
inner join Hierarchy c on c.ClassID = p.ParentClassID
)
--Filter out the redundant hierarchy
,ClassHierarchy(Row, ClassID, ClassName, FullName, ParentClassID, ParentHierarchy) as
(
select ROW_NUMBER() OVER (PARTITION BY ClassID ORDER BY seq DESC), ClassID, ClassName, FullName, ParentClassID, ParentHierarchy
from Hierarchy
)
select Distinct CH.ClassID, CH.ClassName, FullName as Hierarchy, CH.ParentClassID, CH.ParentHierarchy
From Products P,
ProductClasses PC,
ClassHierarchy CH
Where P.ProductID = PC.ProductID AND
PC.ClassID = CH.ClassID AND
CH.Row = 1 AND
--Filter the hierarchy without associated with products
CH.ClassID in (select distinct ClassID from ProductClasses) AND
P.IsActive = 1 AND
P.HasContent = 'Y'
order by ClassID
