SQL Server Recursion select

Question

I have two tables:

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

let's say i have these 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 want a query that will sort the data in this way :

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 that from level 7 , the select must start listing thoses values till level one.

I cannot use CTE because the numbers on recursions is too much limited.

Answer 1

You can use option(maxrecursion 0) to get around the CTE recursion limit. As for the sorting part of the query, see the below

Sample data

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);

The query

;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)