sys_connect_by_path in sql server

Oracle sys_connect_by_path

create table v(o int, k varchar(20));

insert into v values(1,'Barbados');
insert into v values(1,'Rio De Janeiro');
insert into v values(1,'Kapkaupunki');
insert into v values(2,'Hanko');
insert into v values(2,'Helsinki');


select o, max(sys_connect_by_path(k, '/')) as scbp
from (
select o
, k
, row_number() over (partition by o order by k) as rn
from v
)
start with rn = 1
connect by prior rn = rn - 1 and prior o = o
group by o
;

1 /Barbados/Kapkaupunki/Rio De Janeiro
2 /Hanko/Helsinki


SQL Server:

create table dbo.v(o int, k varchar(20))

insert into dbo.v values(1,'Barbados')
insert into dbo.v values(1,'Rio De Janeiro')
insert into dbo.v values(1,'Kapkaupunki')
insert into dbo.v values(2,'Hanko')
insert into dbo.v values(2,'Helsinki')

with x as (
select o
, k
, 1 as level
, row_number() over (partition by o order by k) as rn
, k as scbp
from dbo.v
), cte as (
select o
, k
, level
, rn
, '/' + cast(k as varchar(max)) as scbp
from x
where rn = 1
union all
select x.o
, x.k
, cte.level + 1 as level
, x.rn
, cte.scbp + '/' + cast(x.scbp as varchar(max)) as scbp
from x inner join cte on x.rn - 1 = cte.rn and x.o = cte.o
)
select o, max(scbp)
from cte
group by o
;

1 /Barbados/Kapkaupunki/Rio De Janeiro
2 /Hanko/Helsinki