How to make a select where the order "Group" is shown after "Child Groups"

Question

As it is a system that contains registration of Product Groups. I want to know how to make a select that brings me the information in the following order inside SQL and ordering everything alphabetically.

Summary: Lists a Group, then the Children Groups. Lists the next Group and below, its Children Groups.

EX:

• DRINKS (would be the Parent Group)

• SOFT DRINKS (would be the Grupo Filho);
• ÁGUAS (would be Grupo Filho);
• BEERS (would be the Son Group);

• LANCHES (would be the Parent Group)

• XIS (would be the Son Group);
• HOT DOG (would be the Son Group);

Table Grupoproduct grpDescGroup column

Answer 1

Here is a suggested test using a recursive CTE:

with CTE_Rec as
(
  select
    idGrupoProduto, 
    grpDescGrupo, 
    grpGrupoProdutoPai,
    cast(grpDescGrupo as varchar(1000)) as DescOrdem
  from GrupoProduto
  where grpGrupoProdutoPai is null
  
  union all
  
  select
    g.idGrupoProduto, 
    g.grpDescGrupo, 
    g.grpGrupoProdutoPai,
    cast(c.grpDescGrupo + '|' + g.grpDescGrupo as varchar(1000))
  from CTE_Rec as c
  inner join GrupoProduto as g
    on g.grpGrupoProdutoPai = c.idGrupoProduto
)

select * from CTE_Rec
order by DescOrdem

I hope it helps