Really very good one from you ..Spent lots of time on this..Got the answer.......If you try to understand it will be easy ,anything messask me...CREATE TABLE #TABLE(ID INT,ParentID INT,CatogoryName VARCHAR(100))INSERT INTO #TABLE VALUES(33,0,'Fruits'),(34,0,'Vegetables'),(35,0,'Meats'),(37,33,'Grapes'),(38,33,'Oranges'),(39,33,'Apples'),(40,33,'Bananas'),(41,34,'Celery'),(42,35,'Beef'),(43,35,'Fish'),(44,34,'Carrots'),(45,34,'Onions'),(46,35,'Chicken'),(47,0,'Paper Products'),(48,0,'Electronics')--SELECT * FORM #TABLEDECLARE @InputString VARCHAR(MAX) = STUFF(( SELECT TOP 100 PERCENT ','+ CAST(t.ID AS VARCHAR(MAX)) FROM #TABLE AS t WHERE ParentID =0 ORDER BY ParentID,CatogoryName FOR XML PATH('') ), 1, 1, '') --SELECT @InputStringDECLARE @startingposition INT=1DECLARE @parts nvarchar(4000)DECLARE @Result TABLE (ID nvarchar(4000))DECLARE @FinalResult TABLE (ID INT,ParentID INT,CatogoryName VARCHAR(1024))WHILE @startingposition !=0 BEGIN SELECT @startingposition = CHARINDEX(',',@InputString) IF @startingposition !=0 SELECT @parts = LEFT(@InputString,@startingposition - 1) ELSE SELECT @parts = @InputString INSERT INTO @Result(ID) VALUES(@parts) INSERT INTO @FinalResult(ID,ParentID,CatogoryName) (SELECT * FROM #TABLE WHERE ID = @parts UNION ALL SELECT * FROM #TABLE WHERE ParentID = @parts)--SELECT * FROM @FinalResultSELECT @InputString = RIGHT(@InputString,LEN(@InputString) - @startingposition)ENDSELECT * FROM @FinalResult
---------------Murali KrishnaYou live only once ..If you do it right once is enough.......