Choosing SQL Server Recursion

Question

I have two tables:

entreprises(id, name)
entreprises_struct(id,entreprise_id, entreprise_child_id)

let's say I have this data:

entreprises:
(1,canada)
(2,ontario)
(3,quebec)
(4,ottawa)
(5,toronto)
(6,montreal)
(7,laval)

entreprises_struct
(1,1,1)
(1,1,2)
(1,1,3)
(1,2,4)
(1,2,5)
(1,3,6)
(1,3,7)

I need a query that sorts the data as follows:

montreal  (child level 3)
laval  (child level 3)
quebec (child level 2 and parent of those childs from level 3)
ottawa (child level 3)
toronto (child level 3)
ontario (child level 2 and parent of those childs from level 3)
canada (chil level 1 and parent of thoses childs from level 2)

If I had it from level 7, the choice should begin to list the values ​​of thoses to the first level.

I cannot use CTE because the numbers in recursions are too limited.

Answer 1

You can use option(maxrecursion 0)to get around the CTE recursion constraint. Regarding the sorting part of the request, see below.

Data examples

create table entreprises(id int, name varchar(max));
create table entreprises_struct(id int, entreprise_id int, entreprise_child_id int);
insert entreprises values
(1,'canada'),
(2,'ontario'),
(3,'quebec'),
(4,'ottawa'),
(5,'toronto'),
(6,'montreal'),
(7,'laval');
insert entreprises_struct values
(1,1,1),
(1,1,2),
(1,1,3),
(1,2,4),
(1,2,5),
(1,3,6),
(1,3,7);

Inquiry

;with cte as (
select entreprise_id, level=0,
    path=convert(varchar(max),entreprise_id) + '/'
from entreprises_struct
where entreprise_id =entreprise_child_id -- root
union all
select s.entreprise_child_id, cte.level+1,
    path=cte.path + convert(varchar(max),s.entreprise_child_id) + '/'
from entreprises_struct s
inner join cte on s.entreprise_id = cte.entreprise_id
where s.entreprise_child_id != cte.entreprise_id
)
select e.name
from cte
inner join entreprises e on e.id = cte.entreprise_id
order by path desc
option (maxrecursion 0)