Recursive query with parent-child relation

Question

I am trying to make a recursive query in SQL Server, that display data hierarchically. Here is the structure of the table

    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar(100)] NOT NULL,
    [Parent_Id] [int] NULL,

Each product has a parent. The column Parent_Id content the id of the parent. The parent_id is null for root products.

I want to make a sql query that display products hierarchically. The following image is an example of how the products could be organized.

Products can have products childs.

For the picture above, the query result should be like the following :

id name      parent_id
1  P1        NULL
2  P2        NULL
3  P2-1      2
4  P2-2      2
5  P2-3      2
6  P2-3-1    5
7  P2-3-2    5
8  P3        NULL
9  P3-1      8

Here is the request I wrote to achieve it :

with tree as (select * from products
               union all
               select * from tree where parent_id = tree.id
             )
select * from tree;

But I get a result similar to the following:

1  P1        NULL
2  P2        NULL
8  P3        NULL
3  P2-1      2
4  P2-2      2
5  P2-3      2
9  P3-1      8
6  P2-3-1    5
7  P2-3-2    5

What I want is to group each products sibling so that each product is displayed under its direct parent.

Answer 1

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