Advanced Search

Return to Snippet

Revision: 61910
at January 23, 2013 23:53 by hairajeshk
Initial Code 
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
Initial URL 
http://stackoverflow.com/questions/926033/in-sql-server-how-can-i-select-all-records-in-a-recursive-table
Initial Description 
to fetch all the children in a tree based on parentid
Initial Title 
get all children under a tree
Initial Tags 

                                

                                                            

                                    

                                        

                                        Initial Language
                                        

                                    

                                    
SQL