Here I show you example which will generate navigation list for product through N level. Please check below query for generate N Level product list.
Query:-
-----------------------------------------------
DECLARE @Category TABLE
(
CategoryID INT
,Name VARCHAR(100)
,ParentID INT
)
INSERT INTO @Category(CategoryID,Name,ParentID) VALUES ( 1,'Mobile',0)
INSERT INTO @Category(CategoryID,Name,ParentID) VALUES ( 2,'Nokia',1)
INSERT INTO @Category(CategoryID,Name,ParentID) VALUES ( 3,'Samsung',1)
INSERT INTO @Category(CategoryID,Name,ParentID) VALUES ( 4,'Headphone',0)
INSERT INTO @Category(CategoryID,Name,ParentID) VALUES ( 5,'Nokia N70',2)
INSERT INTO @Category(CategoryID,Name,ParentID) VALUES ( 6,'Nokia N72',2)
INSERT INTO @Category(CategoryID,Name,ParentID) VALUES ( 7,'IBall',4)
;WITH CTE(CategoryID,Name,ParentID,level,ParentName)
AS
(
SELECT CategoryID,Name,ParentID,0,cast('.' + Name + '.' AS VARCHAR(MAX)) FROM @Category WHERE ParentID=0
UNION ALL
SELECT C.CategoryID,C.Name,CT.ParentID,level + 1,CAST (CT.ParentName + '.' + C.Name AS VARCHAR(MAX)) FROM @Category C
INNER JOIN CTE CT ON CT.CategoryID=C.ParentID
)
SELECT SPACE(level * 4) + Name AS Products FROM CTE ORDER BY ParentName
Output
-------------------
