CTE to get all the children (descendants) of the parent

Question

I have this problem that gives me a headache ...

Let's say I have a table with thousands of rows, and the table structure consists of a parent → child relationship.

Relations can reach 6 levels. Here is a sample 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 goes through the whole table to get the relationships and save the "levels" column, this process is very slow (due to loops), and I tried using some cte to get the relationships, but could not miserabily.

So far I have tried this cte, but it does not do what I was hoping for, and also seems to replicate strings ...

 ;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 earlier, we got a process that loops around 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 this, but I suck on it, also, the level columns must be calculated and updated on the table, is this possible?

Here's Sqlfiddle in case someone can 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 is the updated SQL Fiddle .

Also, check out this link for help on the CTE ... They definitely know very well:

Hope this is a 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