Oracle SYS_CONNECT_BY_PATH equivalent query into SQL Server

Question

I'm trying to convert a complex query involving the Oracle SYS_CONNECT_BY_PATH syntax into SQL Server:

    SELECT 
           DISTINCT TO_CHAR(CONCAT(@ROOT, SYS_CONNECT_BY_PATH(CONCAT('C_',X), '.'))) AS X_ALIAS
                , TO_CHAR(CONCAT(@ROOT, PRIOR SYS_CONNECT_BY_PATH(CONCAT('C_',X), '.'))) AS X_ALIAS_FATHER
                , TO_CHAR(X) AS X_ALIAS_LEAF
                , LEVEL AS LVL
      FROM MY_TABLE
 LEFT JOIN MY_TABLE_BIS MY_TABLE_BIS_ALIAS ON MY_TABLE_BIS_ALIAS.MY_ID = COL_X
 LEFT JOIN OTHER_TABLE 
        ON OTHER_TABLE.MY_ID = COL_X
CONNECT BY (PRIOR ID_SON = ID_FATHER)
       AND LEVEL <= MAXDEPTH
START WITH ID_FATHER 
        IN (SELECT AN_ID AS ID_FATHER FROM BIG_TABLE)

Here is what I obtained using this website

WITH n(LEVEL, X_ALIAS, X_ALIAS_FATHER, X_ALIAS_LEAF) AS
      ( SELECT 1, CONCAT('C_',X), CONCAT('C_',X), CAST(X AS VARCHAR(30))
          FROM MY_TABLE
     LEFT JOIN MY_TABLE_BIS MY_TABLE_BIS_ALIAS 
            ON MY_TABLE_BIS_ALIAS.MY_ID = COL_X
     LEFT JOIN OTHER_TABLE 
            ON OTHER_TABLE.MY_ID = COL_X
         WHERE ID_FATHER IN (SELECT AN_ID AS ID_FATHER 
          FROM listAllCfaCfq)
     UNION ALL
        SELECT n.level + 1, n.X_ALIAS + '.' + nplus1.X_ALIAS, n.X_ALIAS_FATHER + '.' + nplus1.X_ALIAS_FATHER, CAST(X AS VARCHAR(30)
          FROM MY_TABLE
     LEFT JOIN MY_TABLE_BIS MY_TABLE_BIS_ALIAS 
            ON MY_TABLE_BIS_ALIAS.MY_ID = COL_X
     LEFT JOIN OTHER_TABLE 
            ON OTHER_TABLE.MY_ID = COL_X AS nplus1, n
         WHERE n.ID_SON = nplus1.ID_FATHER)
        SELECT DISTINCT LEVEL, X_ALIAS, X_ALIAS_FATHER, X_ALIAS_LEAF
         WHERE LEVEL <= @MAXDEPTH;

I changed the name of the tables and I could have done mistakes while doing so, don't hesitate to tell me about that in the comments

Answer 1

CREATE TABLE #MY_TABLE
(
     ID         INT
    ,ID_FATHER  INT
    ,COL_X      INT
)

CREATE TABLE #MY_TABLE_BIS
(
     MY_ID  INT
    ,X      VARCHAR(50)
)

CREATE TABLE #OTHER_TABLE
(
     MY_ID  INT
    ,[ROOT] VARCHAR(50)
)

CREATE TABLE #BIG_TABLE
(
     AN_ID  INT
)
go

DECLARE @MAXDEPTH INT = 10

;WITH
    cte_prepare
    AS
    (
        SELECT
             ID
            ,ID_FATHER
            ,TB.X
            ,OT.[ROOT]
        FROM #MY_TABLE T
        LEFT JOIN #MY_TABLE_BIS TB
                    ON TB.MY_ID = COL_X
        LEFT JOIN #OTHER_TABLE OT
                    ON OT.MY_ID = COL_X
    ),
    cte_connect (LVL, ID, X_ALIAS, X_ALIAS_FATHER, X_ALIAS_LEAF)
    AS
    (
        SELECT 1
            ,T.ID
            ,CAST(CONCAT(T.[ROOT], '.C_', T.X) AS VARCHAR(MAX))
            ,CAST(CONCAT(T.[ROOT], '.') AS VARCHAR(MAX))
            ,CAST(T.X AS VARCHAR(30))
        FROM cte_prepare T
        WHERE T.ID_FATHER IN
            (
                SELECT AN_ID AS ID_FATHER
                FROM #BIG_TABLE
            )

        UNION ALL

        SELECT F.LVL + 1
             ,S.ID
             ,CAST(CONCAT(F.X_ALIAS, '.C_' + S.X) AS VARCHAR(MAX))
             ,CAST(F.X_ALIAS AS VARCHAR(MAX))
             ,CAST(S.X AS VARCHAR(30))
        FROM cte_prepare S
            INNER JOIN cte_connect F
                        ON S.ID_FATHER = F.ID
        WHERE F.LVL < @MAXDEPTH
    )
SELECT DISTINCT
     CT.X_ALIAS
    ,CT.X_ALIAS_FATHER
    ,CT.X_ALIAS_LEAF
    ,CT.LVL
FROM cte_connect CT
-- Uncomment this and set depth when it's greater than 100.
--OPTION (MAXRECURSION 0) -- value between 0 and 32,767 (default is 100), 0 = unlimited

see also https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15

Answer 2

All can be solved using several FUNCTIONs, and it is simple with some recursion, (note that max recursion level in T-SQL is 32)

Assuming we have the following table: (to a very small company)

TableName: Employees
id.....name..............manager_id
1      Big Boss          NULL
2      Sales Manager     1
3      Support Manager   1
4      R&D Manager       1               
5      Sales man         2
6      Support man       3
7      R&D Team leader   4
8      QA Team leader    4
9      C Developer       7
10     QA man            8
11     Java Developer    7      

We just need a function to check if there is link between 2 ids, and another function to give the path from one to the other.

The first function is very simple using recursive :

Create Function dbo.Do_WE_Have_path(@id int, @boss_id int, @max_level int) returns int
Begin
  declare @res int, @man int
  set @res = 0
  if @id = @boss_id 
    set @res = 1
  else if @max_level > 0 
  Begin
    Select @man=manager_id from Employees where id=@id
    set @res = Do_WE_Have_path(@man, @boss_id, @max_level-1) --recursion
  End
  return res 
End

Using the above Function we can select all entities with a connection shorter or equal to a specified level, so now we can write a method which build the path if it exists, note that a not exist path should be filter using the above method.

Create Function dbo.Make_The_path(@id int, @boss_id int, @max_level int) returns varchar(max)
Begin
  declare @res varchar(max), @man int
  select @res = name from Employees where id=@id
  if max_level > 0 AND @id <> @boss_id
  Begin 
    select @man = manager_id from Employees where id = @id
    set @res = dbo.Make_The_path(@man, @boss_id, max_level-1) + '/' + @res
  End
  return @res
End

Now we can use both functions to get the path from the boss to the workers:

Select dbo.Make_The_path(id, 1, 3) Where Do_WE_Have_path(id, 1, 3)=1    

Both Function can be merged to a one, and maybe you'll need to write it again for every structure, but the important thing is that it is possible.

Answer 3

You probably want an recursive CTE for the heirarchial part, and then use FOR XML for the path building. FOR XML was intended to convert your results into XML, but you can use it to transform your results in to all kinds of interesting text formats.

FOR XML PATH clause in SQL Server

FOR XML (SQL Server) Microsoft Reference

Answer 4

Here https://riptutorial.com/sql/example/5603/oracle-connect-by-functionality-with-recursive-ctes you will find a complete example of converting CONNECT BY queries to recursive CTE using SQLServer syntax, without any need to write extra functions.