http://stackoverflow.com/questions/926033/in-sql-server-how-can-i-select-all-records-in-a-recursive-table

inline code if url is not working:
CREATE TABLE TestTable
( 
    ID int primary key NOT NULL,
    ParentID int
)

INSERT INTO TestTable VALUES (0, null)
INSERT INTO TestTable VALUES (1, 0)
INSERT INTO TestTable VALUES (2, 0)
INSERT INTO TestTable VALUES (3, 1)
INSERT INTO TestTable VALUES (4, 3)


-- Get branch
;WITH TreeRecCTE (ID, ParentID, IDPath)
AS
(
   SELECT ID, ParentID, CONVERT(varchar(max), ID) As IDPath
      FROM TestTable
      WHERE ParentID IS NULL
   UNION ALL
   SELECT
    		Child.ID,
    		Child.ParentID,
    		Parent.IDPath + '.' + CONVERT(varchar(100),Child.ID) As IDPath
    	FROM TestTable As Child INNER JOIN TreeRecCTE AS Parent ON Child.ParentID = Parent.ID
  )
SELECT * FROM TreeRecCTE WHERE IDPath LIKE '%.1.%' ORDER BY ParentID ASC 


-- Get complete tree:
;WITH TreeRecCTE (ID, ParentID, IDPath)
AS
(
   SELECT ID, ParentID, CONVERT(varchar(max), ID) As IDPath
      FROM TestTable
      WHERE ParentID IS NULL
   UNION ALL
   SELECT
    		Child.ID,
    		Child.ParentID,
    		Parent.IDPath + '.' + CONVERT(varchar(100),Child.ID) As IDPath
    	FROM TestTable As Child INNER JOIN TreeRecCTE AS Parent ON Child.ParentID = Parent.ID
  )
SELECT * FROM TreeRecCTE ORDER BY ParentID ASC

http://stackoverflow.com/questions/926033/in-sql-server-how-can-i-select-all-records-in-a-recursive-table

to fetch all the children in a tree based on parentid

get all children under a tree

SQL