Here is my sample data table :

create or replace table test_case (
col_a string,
col_b string,
match_flag string);

insert into test_case
(col_a, col_b, match_flag)
values
('a', 'b', 'Y'),
('b', 'c', 'Y'),
('c', 'a', 'Y'),
('a', 'z', 'N');

As you can see, the values in col_a , col_b is considered a match if match_flag=’Y’. First three rows are considered a match. As in first row, ‘a’ is match to ‘b’. similarly, 2nd and 3rd row gives, ‘b’ is match to ‘c’ and ‘c’ is match to ‘a’.

I am trying to write a query which outputs a concatenation of values for all matches. For example, in the above case, I am looking for following output:

col_a col_b col_c output
a b ‘Y’ ‘a-b-c’
b c ‘Y’ ‘a-b-c’
c a ‘Y’ ‘a-b-c’
a z ‘N’ ‘na’

My query seems to going to infinite loop.

WITH RECURSIVE match_cte AS (
    -- Base case: start with the first row where match_flag = 'Y'
    SELECT col_a, col_b, col_a AS start_col, col_b AS match_path
    FROM test_case
    WHERE match_flag = 'Y'
    
    UNION ALL
    
    -- Recursive case: find matches for the current match_path and continue
    SELECT t.col_a, t.col_b, m.start_col, 
           CASE 
               WHEN m.match_path LIKE '%' || t.col_a || '%' THEN m.match_path
               ELSE m.match_path || '-' || t.col_b 
           END AS match_path
    FROM match_cte m
    JOIN test_case t ON m.col_b = t.col_a
    WHERE t.match_flag = 'Y'
)

-- Final output with results for each original row in the table
SELECT t.col_a, t.col_b, t.match_flag,
       CASE 
           WHEN t.match_flag = 'Y' THEN 
               (SELECT DISTINCT match_path 
                FROM match_cte m 
                WHERE m.start_col = t.col_a 
                ORDER BY LENGTH(m.match_path) DESC LIMIT 1)
           ELSE 'na'
       END AS output
FROM test_case t;

Any thoughts on how I can achieve the desired output ?