SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64) 
 Apr 11 2022 16:24:07 
 Copyright (C) 2019 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



create table loan_collateral_link (
loan_id varchar(10),
collateral_id varchar(10)
);

insert into loan_collateral_link values
('Loan1', 'Col1'),
('Loan2', 'Col1'),
('Loan2', 'Col3'),
('Loan3', 'Col2'),
('Loan4', 'Col2'),
('Loan5', 'Col1'),
('Loan5', 'Col3'),

-- plus an extra group with some more interesting links
('Loan80', 'Col80'),
('Loan81', 'Col80'),
('Loan80', 'Col81'),
('Loan81', 'Col81'),
('Loan82', 'Col81')
12 rows affected

WITH loans AS (
    SELECT 
        collateral_id,
        ',' + STRING_AGG(loan_id, ',') WITHIN GROUP (ORDER BY loan_id) + ',' AS loans,
        COUNT(loan_id) AS loan_count
    FROM loan_collateral_link
    GROUP BY collateral_id
),
loans_rn AS (
    SELECT
        *,
        DENSE_RANK() OVER (ORDER BY loan_count DESC) AS rn
    FROM loans
),
cte AS (
    SELECT 
        collateral_id, 
        loans, 
        loan_count, 
        collateral_id AS gid 
    FROM loans_rn 
    WHERE rn = 1
    UNION ALL
    SELECT 
        l.*, 
        cte.gid 
    FROM loans l
    JOIN cte ON l.loan_count < cte.loan_count 
                AND l.collateral_id != cte.collateral_id 
                AND cte.loans LIKE '%' + l.loans + '%'
),
collaterals AS (
    SELECT collateral_id, gid, loans FROM cte
    UNION ALL
    SELECT collateral_id, collateral_id, loans
    FROM loans WHERE NOT EXISTS (
        SELECT 1 
        FROM cte 
        WHERE cte.collateral_id = loans.collateral_id
    )
)
SELECT 
    item_id,
    'Group' + CAST(DENSE_RANK() OVER (ORDER BY gid) AS VARCHAR(MAX)) AS group_id
FROM (    
    SELECT 
        collateral_id AS item_id, 
        gid 
    FROM collaterals
    UNION ALL
    SELECT DISTINCT 
        l.loan_id, 
        (SELECT TOP 1 
             gid 
         FROM collaterals c 
         WHERE c.loans LIKE '%,'+l.loan_id + ',%')
    FROM loan_collateral_link l
) groups
ORDER BY group_id, item_id


item_id
group_id


Col1
Group1

Col3
Group1

Loan1
Group1

Loan2
Group1

Loan5
Group1

Col2
Group2

Loan3
Group2

Loan4
Group2

Col80
Group3

Col81
Group3

Loan80
Group3

Loan81
Group3

Loan82
Group3

item_id	group_id
Col1	Group1
Col3	Group1
Loan1	Group1
Loan2	Group1
Loan5	Group1
Col2	Group2
Loan3	Group2
Loan4	Group2
Col80	Group3
Col81	Group3
Loan80	Group3
Loan81	Group3
Loan82	Group3