SQL Server Cte Parent Child Recursive

CTE to get all children (descendants) of a parent

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!

Recursive CTE for parent and child relationship in SQL Server

You could move the start condition inside the CTE:

; with  parents as
        ( 
        select  child_product_id
        ,       parent_product_id
        from    product
        where   child_product_id = 392193
                and parent_product_id = 392193
        union all 
        select  e.child_product_id
        ,       e.parent_product_id
        from    parents m
        join    product e
        on      e.parent_product_id = m.child_product_id
        )
select  *
from    parents  
option  (maxrecursion 0)

An index on (parent_product_id, child_product_id) would help.

Usually, a child record refers to the primary key of its parent record. In your case, there's something unusual going on, with the parent having a "child_product_id". Some more information on this construct would clarify your question.

Recursive CTE to find Total for all children parent and depth

One option is to use the data type hierarchyid

Example

;with cteP as (
      Select ID
            ,parentid 
            ,Name 
            ,HierID = convert(hierarchyid,concat('/',ID,'/'))
      From   #Temp 
      Where  parentid is null
      Union  All
      Select ID  = r.ID
            ,parentid  = r.parentid 
            ,Name   = r.Name
            ,HierID = convert(hierarchyid,concat(p.HierID.ToString(),r.ID,'/'))
      From   #Temp r
      Join   cteP p on r.parentid  = p.ID)
Select ID
      ,parentid
      ,Name 
      ,Path      = HierID.ToString()
      ,Depth     = ( Select max(HierID.GetLevel() ) from cteP where HierID.ToString() like A.HierID.ToString()+'%') - HierID.GetLevel()
      ,Lvl       = HierID.GetLevel()
      ,ChildCnt  = ( Select count(*) from cteP where HierID.ToString() like A.HierID.ToString()+'%') -1
      ,ParentCnt = len(HierID.ToString()) - len(replace(HierID.ToString(),'/','')) - 2
 From cteP A
 Order By A.HierID

Results

How to expand parent-child hierarchy recursively using CTE

You need a recursive CTE for this:

with cte as (
      select id, id as childid, 1 as lev
      from t
      -- where parentid is null
      union all
      select cte.id, t.id, lev + 1
      from cte join
           t
           on cte.childid = t.parentid
      where lev < 10 and t.id <> cte.id
     )
select id, childid
from cte
order by id, childid;

Here is a db<>fiddle.

Recursive Parent/Child in same table query in SQL where parent is PK

This produces the results you are asking for:

with cte as (
      select idchild, idparent,
             convert(varchar(max), idchild) as children
      from family f
      where not exists (select 1 from family f2 where f2.idparent = f.idchild)
      union all
      select f.idchild, f.idparent,
             concat(f.idchild, ',', cte.children)
      from cte join
           family f
           on cte.idparent = f.idchild
     )
 select *
 from cte
 order by idchild;

Here is the SQL Fiddle.

Recursive query with parent-child relation

Just another option using the data type hierarchyid

There are some additional features and functions associated with hierarchyid

Example

-- Optional See 1st WHERE
Declare @Top int = null  --<<  Sets top of Hier Try 2

;with cteP as (
      Select ID
            ,parent_id 
            ,Name 
            ,HierID = convert(hierarchyid,concat('/',ID,'/'))
      From   YourTable 
      Where  IsNull(@Top,-1) = case when @Top is null then isnull(parent_id ,-1) else ID end
      --Where parent_id is null  -- Use this where if you always want the full hierarchy
      Union  All
      Select ID  = r.ID
            ,parent_id  = r.parent_id 
            ,Name   = r.Name
            ,HierID = convert(hierarchyid,concat(p.HierID.ToString(),r.ID,'/'))
      From   YourTable r
      Join   cteP p on r.parent_id  = p.ID)
Select Lvl   = HierID.GetLevel()
      ,ID
      ,parent_id
      ,Name  
 From cteP A
 Order By A.HierID

Results

Lvl ID  parent_id   Name
1   1   NULL        P1
1   2   NULL        P2
2   3   2           P2-1
2   4   2           P2-2
2   5   2           P2-3
3   6   5           P2-3-1
3   7   5           P2-3-2
1   8   NULL        P3
2   9   8           P3-1

Just for fun, If I set @Top to 2, the results would be

Lvl ID  parent_id   Name
1   2   NULL        P2
2   3   2           P2-1
2   4   2           P2-2
2   5   2           P2-3
3   6   5           P2-3-1
3   7   5           P2-3-2

CTE to get all children and nested children of every parent

In first part of cte select all the rows with ownid as root id. Then in second part (after union all) select parentid as rootid.

Schema and insert statements:

 create table users (UserID int,  ParentID int);
 insert into users values (1,        NULL);
 insert into users values (2,        1);
 insert into users values (3,        1);
 insert into users values (4,        2);
 insert into users values (5,        2);
 insert into users values (6,        5);
 insert into users values (7,        6);
 insert into users values (8,        6);
 insert into users values (9,        NULL);

Query:

 with cte as
 (
   select userid rootid, userid, parentid from users 
   union all
   select cte.rootid rootid, users.userid, users.parentid from users
   inner join cte on users.parentid=cte.userid
 )
 select rootid parentid,userid from cte
 order by rootid ,userid
 option (maxrecursion 0)

Output:

---