• Identify first reversal for each payment_ref
• Then match first reversal with the corresponding payment entry

Fiddle

WITH first_reversal AS (
    SELECT 
        Payment_Ref,
        Payment_Log,
        Log_Date,
        activity_type,
        Source,
        [User], -- 'User' is a reserved word, so we use square brackets
        amount,
        credit_or_debit,
        Comment,
        ROW_NUMBER() OVER (PARTITION BY Payment_Ref ORDER BY Log_Date) AS rev_rank
    FROM test
    WHERE activity_type = 'Payment_Rev' AND Payment_Log LIKE '%-Reverse'
)
-- Select the first reversal and its corresponding 'Pay' log
SELECT 
    pr.Payment_Ref,
    REPLACE(pr.Payment_Log, '-Reverse', '-Pay') AS Payment_Log,
    t.Log_Date,
    t.activity_type,
    t.Source,
    t.[User],
    t.amount,
    t.credit_or_debit,
    t.Comment
FROM first_reversal pr
JOIN test t ON pr.Payment_Ref = t.Payment_Ref
           AND REPLACE(pr.Payment_Log, '-Reverse', '-Pay') = t.Payment_Log
WHERE pr.rev_rank = 1;

Output