Stored Procedure with CTE to query Category , Sub Category , Sub Sub Category

Question

I have written an SP with CTE.

CREATE PROC [dbo].[CategoryListShow]
 @id AS INT
 AS
WITH CategoryList
AS
(
  SELECT parent.Categoryid, CONVERT(varchar(50),parent.CategoryName)
  as
  Name, parent.CategoryParentid
  FROM Category as parent
  WHERE parent.CategoryParentid IS NULL

  UNION ALL

  SELECT child.Categoryid, CONVERT(varchar(50),CL.Name + ' > ' + child.CategoryName)
   as Name, child.CategoryParentid
   FROM Category as child

   INNER JOIN CategoryList as CL ON child.CategoryParentid = CL.Categoryid

   WHERE child.CategoryParentid IS NOT NULL
)
   SELECT Name from CategoryList option (maxrecursion 0)

How can I achieve the desired output? For example, if user types id = 14111 then output should be this: Everything Else > Test Auctions > General

My table structure:

Thanks

Answer 1

You can do this

;with
CTE_Data as 
(
    select C.CategoryID, cast(C.CategoryName as nvarchar(max)) as CategoryName
    from Category as C
    where C.CategoryID = C.CategoryParentId

    union all

    select C.CategoryID, CD.CategoryName + ' > ' + C.CategoryName
    from Category as C
        inner join CTE_Data as CD on CD.CategoryID = C.CategoryParentId
    where C.CategoryID <> C.CategoryParentId
)
select * 
from CTE_Data
where CategoryID = @ID

or the other way around:

;with
CTE_Data as 
(
    select C.CategoryID, cast(C.CategoryName as nvarchar(max)) as CategoryName, C.CategoryParentId
    from Category as C
    where C.CategoryID = @ID

    union all

    select C.CategoryID,  cast(C.CategoryName as nvarchar(max)) + ' > ' + CD.CategoryName, C.CategoryParentId
    from Category as C
      inner join CTE_Data as CD on CD.CategoryParentId = C.CategoryID
    where CD.CategoryID <> C.CategoryID
)
select CategoryName
from CTE_Data
where CategoryID = CategoryParentId

SQL FIDDLE