Posted By

hairajeshk on 01/23/13


Tagged

get tree children


Versions (?)

get all children under a tree


 / Published in: SQL
 

URL: 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

  1. http://stackoverflow.com/questions/926033/in-sql-server-how-can-i-select-all-records-in-a-recursive-TABLE
  2.  
  3. inline code IF url IS NOT working:
  4. CREATE TABLE TestTable
  5. (
  6. ID int PRIMARY KEY NOT NULL,
  7. ParentID int
  8. )
  9.  
  10. INSERT INTO TestTable VALUES (0, NULL)
  11. INSERT INTO TestTable VALUES (1, 0)
  12. INSERT INTO TestTable VALUES (2, 0)
  13. INSERT INTO TestTable VALUES (3, 1)
  14. INSERT INTO TestTable VALUES (4, 3)
  15.  
  16.  
  17. -- Get branch
  18. ;WITH TreeRecCTE (ID, ParentID, IDPath)
  19. AS
  20. (
  21. SELECT ID, ParentID, CONVERT(varchar(max), ID) AS IDPath
  22. FROM TestTable
  23. WHERE ParentID IS NULL
  24. UNION ALL
  25. SELECT
  26. Child.ID,
  27. Child.ParentID,
  28. Parent.IDPath + '.' + CONVERT(varchar(100),Child.ID) AS IDPath
  29. FROM TestTable AS Child INNER JOIN TreeRecCTE AS Parent ON Child.ParentID = Parent.ID
  30. )
  31. SELECT * FROM TreeRecCTE WHERE IDPath LIKE '%.1.%' ORDER BY ParentID ASC
  32.  
  33.  
  34. -- Get complete tree:
  35. ;WITH TreeRecCTE (ID, ParentID, IDPath)
  36. AS
  37. (
  38. SELECT ID, ParentID, CONVERT(varchar(max), ID) AS IDPath
  39. FROM TestTable
  40. WHERE ParentID IS NULL
  41. UNION ALL
  42. SELECT
  43. Child.ID,
  44. Child.ParentID,
  45. Parent.IDPath + '.' + CONVERT(varchar(100),Child.ID) AS IDPath
  46. FROM TestTable AS Child INNER JOIN TreeRecCTE AS Parent ON Child.ParentID = Parent.ID
  47. )
  48. SELECT * FROM TreeRecCTE ORDER BY ParentID ASC

Report this snippet  

You need to login to post a comment.