Blog - Single Post

Below is a simple CTE (Common Table Expression) Example to retrieve all descendant categories from Category table.

WITH    Categories
          AS ( SELECT   C.ParentCategoryID ,
                        C.CategoryID ,
                        C.Name,
                        CAST(C.Name AS VARCHAR(MAX)) AS Level
               FROM     dbo.Category C
               WHERE    C.CategoryID = 3885
               UNION ALL
               SELECT   C1.ParentCategoryID ,
                        C1.CategoryID ,
                        C1.Name,
                         M.Level + ' >> ' + CAST(C1.Name AS VARCHAR(MAX)) 
               FROM     dbo.Category C1
                        INNER JOIN Categories M ON M.CategoryID = C1.ParentCategoryID
             )
    SELECT  *
    FROM    Categories;