I have a dataset in which i am matching rows together using different methods. Some of the results of these methods overlap. I am trying to use a recursive query to group lines that do overlap. Here is a sample of the data table.

Here is my query I have been trying to get to work. I thought I could use a LIKE operator to match the Line_ID in Anchor Query to the Line_ID_Matches field in the other query. However I cannot get it work despite my different combinations. The results keep coming back as max # of recursions reached if I don’t set OPTION(MAXRECURSION 0).

WITH cte_qry as (
SELECT
    1 as Level
    ,DENSE_RANK() OVER(ORDER BY lines) as group_id
    ,Line_ID
    ,Line_ID_Matches
FROM
    #consol2
WHERE
    method = 'sp1'
UNION ALL
SELECT
    cte_qry.Level + 1
    ,cte_qry.group_id
    ,t.Line_ID
    ,t.Line_ID_Matches
FROM
    cte_qry 
    INNER JOIN #consol2 t
    On t.Line_ID_Matches like CONCAT('%',Line_ID,'%')
)
SELECT
    *
INTO
    #results
FROM
    cte_qry
OPTION(MAXRECURSION 0)

I was hoping to get something like this. You can see Group_ID #3 has both “202401-0046799, 202401-0002634” from method ‘sp1’ & “202401-0046799, 202401-0002634, 202401-0036365” from method ‘sp2’ because they both share two common Line_IDs.

Can anyone provide me any suggestions on this to get it to work or modified to get the desired effect?

Thank you