2 Levels Common Table Expressions (CTE) Sample

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