
-- check the below sample input & output
-- Input Data
DECLARE @Category TABLE(category_id int, category_name varchaR(10), parent_id int)
insert into @Category
SELECT 1, 'Mens', NULL union all
SELECT 2, 'Ladies', NULL union all
SELECT 3, 'Clothing', 1 union all
SELECT 4, 'Shirts', 3 union all
SELECT 5, 'Jewellery', 2 union all
SELECT 6, 'Rings', 5
-- Hierarchical query
;WITH RecCTE
AS (SELECT category_id AS ID, CAST( category_name AS VARCHAR(500)) AS Chaining, Parent_id
FROM @Category
WHERE parent_id IS NULL
UNION ALL
SELECT category_id , CAST( Chaining+'-->'+Category_name AS VARCHAR(500)), c.Parent_id
FROM RecCTE r
JOIN @Category c ON c.parent_id=r.Id
)
SELECT * FROM RecCTE
--Output
ID Chaining Parent_id
1 Mens NULL
2 Ladies NULL
5 Ladies-->Jewellery 2
6 Ladies-->Jewellery-->Rings 5
3 Mens-->Clothing 1
4 Mens-->Clothing-->Shirts 3
Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif
Kashif, if this helps please login to Mark As Answer. | Alert Moderator