CTE to get all children (descendants) of a parent

common-table-expression, sql-server

Solution

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!

Problem

I've this problem that's giving me a headache... Say, i've got a table with some thousands of rows and the structure of the table consists on a parent -> child relationship. The relationships can go up to 6 levels. Here's a sample of the table structure: ``` ProductId | ParentId | Levels 1174 0 1174 311 1174 311, 1174 1186 311 1186, 311, 1174 448 1186 448, 1186, 311, 1174 3365 448 3365, 448, 1186, 311, 1174 ``` We got a process that loops through the entire table to get the relationships and saves the "levels" column, this process is really slow ( because of the loops ) and i've tried with some cte to get the relationships but failed miserabily. So far i've tried this cte but it doesn't do what i was hoping for and also, it seems to be replicating rows... ``` ;With Parents(ProductId, ParentId, Levels) As( Select ProductId, ParentId, Levels From Products Where ParentId = 0 Union All Select p.ProductId, p.ParentId, p.Levels From Products p Join Parents cte On cte.ProductId = p.ParentId ) Select * From Parents ``` As i mentioned early, we got a process that loops the table, it does its job but it can take up to 30 minutes, my question is is there a better way to do this? i know that CTE allows me to do it but i suck at it, also, the levels column should be calculated and updated on the table, is it possible? Here's a Sqlfiddle in case someone could help, thanks!

Original source