I am trying to group a list of accounts if they share same phone numbers directly or indirectly using SQL query in recursive way. I was trying traversing the parent-child hierarchy like most SQL recursive examples do, but it doesn’t work because each parent in my case could have multiple children.

e.g.
AccountID PhoneNumber
1 1111111111
1 2222222222
2 1111111111
2 4444444444
3 3333333333
3 4444444444

I have tried common approach to this recursive problem in SQL by defining base case and recursive case, but I am getting “The maximum recursion 100 has been exhausted before statement completion.” error.

-- Step 1: Define a recursive CTE to find connected components
WITH ConnectedAccounts AS (
    -- Base case: Select each account and its direct connections
    SELECT 
        a.AccountID AS RootAccount,
        a.AccountID,
        a.PhoneNumber,
        level = 0
    FROM 
        AccountPhoneNumbers a
    
    UNION ALL
    
    -- Recursive case: Connect accounts sharing a phone number
    SELECT 
        c.RootAccount,
        b.AccountID,
        b.PhoneNumber,
        c.level + 1
    FROM 
        AccountPhoneNumbers b 
        JOIN ConnectedAccounts c ON b.PhoneNumber = c.PhoneNumber
    WHERE 
        b.AccountID <> c.AccountID  -- Prevent looping back to the same account
),

-- Step 2: Select distinct groupings from the recursive result
DistinctGroups AS (
    SELECT DISTINCT
        RootAccount,
        AccountID
    FROM
        ConnectedAccounts 
)

-- Step 3: Aggregate accounts into groups based on their connected root account
SELECT
    RootAccount,
    STRING_AGG(AccountID, ',') AS ConnectedAccounts
FROM
    DistinctGroups 
GROUP BY
    RootAccount