CTE to get all children (descendants) of a parent

by

This should do it:

WITH MyTest as
(
  SELECT P.ProductID, P.ParentID, CAST(P.ProductID AS VarChar(Max)) as Level
  FROM Products P
  WHERE P.ParentID = 0

  UNION ALL

  SELECT P1.ProductID, P1.ParentID, CAST(P1.ProductID AS VarChar(Max)) + ', ' + M.Level
  FROM Products P1  
  INNER JOIN MyTest M
  ON M.ProductID = P1.ParentID
 )
SELECT * From MyTest

And here’s the updated SQL Fiddle.

Also, check out this link for help with CTEs… They’re definitely good to know:

Hope this does the trick!

More Related Contents:

  1. SQL Server CTE and recursion example
  2. Which are more performant, CTE or temporary tables?
  3. CTE Recursion to get tree hierarchy
  4. Infinite loop CTE with OPTION (maxrecursion 0)
  5. Recursive sum in tree structure
  6. SQL Recursive CTE: Finding objects linked by property
  7. How to set the maxrecursion option for a CTE inside a Table-Valued-Function
  8. What do Clustered and Non-Clustered index actually mean?
  9. Regex pattern inside SQL Replace function?
  10. Does T-SQL have an aggregate function to concatenate strings? [duplicate]
  11. How to create materialized views in SQL Server?
  12. How can I clear the SQL Server query cache?
  13. What columns generally make good indexes?
  14. What is a free tool to compare two SQL Server Databases? [closed]
  15. SQL Server: Database stuck in “Restoring” state
  16. Split one column into multiple rows
  17. #DELETE viewing SQL Server table in Access
  18. Get ROWS as COLUMNS (SQL Server dynamic PIVOT query)
  19. SQL Server (localdb)\v11.0 explained
  20. How to store directory / hierarchy / tree structure in the database?
  21. SQL Server – join rows into comma separated list
  22. Which is quicker COALESCE OR ISNULL?
  23. Merge multiple rows into a single row
  24. “Prevent saving changes that require the table to be re-created” negative effects
  25. What are the differences between Merge Join and Lookup transformations in SSIS?
  26. Table Valued Function Killing My Query Performance
  27. freeTDS not using its config
  28. @@ERROR and/or TRY – CATCH
  29. How to make a passthrough / passthru query editable?
  30. How to do Sql Server CE table update from another table

Leave a Comment