Check this:
DECLARE @hierarchy TABLE (id INT IDENTITY(1,1), Parent_id INT, Category NVARCHAR(50))
INSERT INTO @hierarchy (Parent_id, Category)
VALUES(0, 'Clothes'),
(1, 'Women'),
(2, 'Jewellery'),
(0, 'Mobiles')
SELECT a.id, a.Parent_id, a.Category, ISNULL(b.Category, 'none') As Parent_Name
FROM @hierarchy AS a LEFT JOIN @hierarchy AS b ON a.Parent_id = b.id
I'd strongly recommend to read this:
Visual Representation of SQL Joins[
^]