hi i want hierarchy with count
like,
fashion (main category)
--For men(sub category)
----clothes(10)
----accessories(12)
--For Women
----clothes(7)
----accessories(4)
i want to do it with cte or cursor
i have tried with cte...
CREATE PROCEDURE [dbo].[ts_orc_navigation_relatedproducts]
@categoryid int
AS
BEGIN
SET NOCOUNT ON;
WITH Emp_CTE AS (
SELECT categoryid, parentcategoryid, name , 1 as catglevel
FROM orc_categories
WHERE parentcategoryid = @categoryid
UNION ALL
SELECT e.categoryid, e.parentcategoryid, e.name , ecte.catglevel +1
FROM orc_categories e
INNER JOIN Emp_CTE ecte ON ecte.categoryid = e.parentcategoryid
)
SELECT
c.parentcategoryid as ParentCategoryID,c.categoryid AS CategoryID, c.name +' (' + cast(count(m.categoryid) as varchar(50)) + ')' as Name
from Emp_CTE c
left join orc_product_category_mapping m on m.categoryid=c.categoryid
group by c.name , c.categoryid , c.ParentCategoryID, catglevel order by catglevel
END
i get output like
parentcategoy categoryid
52 42 For Men (0)
52 41 For Women (1)
41 44 Accessories (1)
42 49 Accessories (1)
41 43 Beauty (5)
42 48 Beauty (1)
41 46 Clothing (1)
42 51 Clothing (3)
41 45 Clutch Bags (1)
42 50 Clutch Bags (11)
41 47 Eye Wear (14)
thanks
