CTE to get all children (descendants) of a parent

Question

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!

Answer 1

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!

Answer 2

;With Parents(ProductId, ParentId, Level, levels) As(   Select ProductId, ParentId, 0,       cast(ltrim(str(productId,8,0)) as varchar(max))   From Products   Where ParentId = 0    Union All   Select p.ProductId, p.ParentId,        par.Level + 1,       cast( levels + ', ' + ltrim(str(productId,8,0)) as varchar(max))   From Products p      Join Parents par         On par.ProductId = p.ParentId   )   Select * From Parents   Order By Level