Translate query from Oracle to SQL Server

Question

I'm trying to use the below query into HPALM.

However this is a Oracle syntax and I need to translate it to SQL Server.

Indeed, SYS_CONNECT_BY_PATH is an Oracle feature.

SELECT ls_father_id,ls_id,substr(SYS_CONNECT_BY_PATH(ls_name,'\'),2) "Path"
FROM lists
START WITH ls_name in (select ls_name from lists where ls_father_id = 0)
CONNECT BY PRIOR ls_id=ls_father_id

It is it possible to translate it? If yes, how to do?

Thank you

Answer 1

You can use recursive CTE for this (in Oracle also), so you can check it first in your current Oracle database.

with a (ls_father_id, ls_id, path_) as (
  select
    ls_father_id
    , ls_id
    , ls_name as path_
  from lists
  where ls_name in (select f.ls_name from lists f where f.ls_father_id = 0)

  union all

  select 
    b.ls_father_id
    , b.ls_id
    , a.path_ || '\' || b.ls_name
  from b
    join a
      on b.ls_father_id = a.ls_id
)
select *
from a

Example below

create table t
as
with a (ls_id, ls_father_id, ls_name) as (
  select 1, 0   , 'aaa' from dual union all
  select 2, 1   , 'bb'  from dual union all
  select 3, 0   , 'c'   from dual union all
  select 4, 2   , 'cca' from dual union all
  select 5, 4   , 'vvv' from dual union all
  select 6, 3   , 'f'   from dual union all
  select 7, 1   , 'ee'  from dual
)
select *
from a

select
  t.*
  , substr(sys_connect_by_path(ls_name, '\'), 2) as path_
from t
start with ls_name in (select f.ls_name from t f where f.ls_father_id = 0)
connect by prior ls_id = ls_father_id
order by ls_id

LS_ID | LS_FATHER_ID | LS_NAME | PATH_         
----: | -----------: | :------ | :-------------
    1 |            0 | aaa     | aaa           
    2 |            1 | bb      | aaa\bb        
    3 |            0 | c       | c             
    4 |            2 | cca     | aaa\bb\cca    
    5 |            4 | vvv     | aaa\bb\cca\vvv
    6 |            3 | f       | c\f           
    7 |            1 | ee      | aaa\ee        

with a (ls_id, ls_father_id, path_) as (
  select
    t.ls_id
    , t.ls_father_id
    , cast(ls_name as varchar(1000))
  from t
  where ls_name in (select f.ls_name from t f where f.ls_father_id = 0)
  
  union all
  
  select
    t.ls_id, t.ls_father_id, a.path_ || '\' || t.ls_name
  from a
    join t
      on a.ls_id = t.ls_father_id
)
select *
from a
order by ls_id

LS_ID | LS_FATHER_ID | PATH_         
----: | -----------: | :-------------
    1 |            0 | aaa           
    2 |            1 | aaa\bb        
    3 |            0 | c             
    4 |            2 | aaa\bb\cca    
    5 |            4 | aaa\bb\cca\vvv
    6 |            3 | c\f           
    7 |            1 | aaa\ee        

db<>fiddle here