Get all child nodes of parent in self join table Tree and vice versa

Lets say we have a table with fileds "category_id, category_name, parentid" now we wants to retrieve all child nodes of specific node then below query will help you.

;WITH Descendants AS (
 SELECT  p.category_name
                , p.category_id
                , 0 AS HLevel
 FROM dbo.categorymaster p  
 WHERE category_id = '7'
 UNION ALL
 SELECT p.category_name
                , p.category_id
                , H.HLevel+1
 FROM dbo.categorymaster p  
 INNER JOIN Descendants H
 ON H.category_id=p.parentid  
)  
SELECT category_id, REPLICATE('     ', Hlevel) + category_name AS category_name
FROM Descendants d

To get all ancestors of a node:

;WITH Ancestors
AS 
( 
 SELECT  p.category_name
  , p.category_id
  , parentid
  , 1 AS HLevel
 FROM dbo.categorymaster p  
 WHERE category_id = 9
 UNION ALL
 SELECT p.category_name
  , p.category_id
  , p.parentid
  , H.HLevel+1
 FROM dbo.categorymaster p  
 INNER JOIN Ancestors H
 ON H.parentid=p.category_id  
)  
SELECT category_name, category_id, HLevel
FROM Ancestors c 

References:

http://jsimonbi.wordpress.com/2011/01/14/sql-hierarchies-parent-child/
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html