Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name (ID, PAYMENT_METHOD) AS
SELECT 1, 'CASH' FROM DUAL UNION ALL
SELECT 1, 'VISA' FROM DUAL UNION ALL
SELECT 1, 'CASH' FROM DUAL UNION ALL
SELECT 2, 'CASH' FROM DUAL UNION ALL
SELECT 2, 'CASH' FROM DUAL UNION ALL
SELECT 2, 'CASH' FROM DUAL UNION ALL
SELECT 3, 'CHEQUE' FROM DUAL UNION ALL
SELECT 3, 'VISA' FROM DUAL UNION ALL
SELECT 3, 'VISA' FROM DUAL UNION ALL
SELECT 4, 'CASH' FROM DUAL UNION ALL
SELECT 4, 'CASH' FROM DUAL UNION ALL
SELECT 4, 'CASH' FROM DUAL;
12 rows affected

SELECT id,
       MIN(payment_method) KEEP (DENSE_RANK FIRST ORDER BY ROWNUM)
         AS payment_method
FROM   table_name
GROUP BY id
HAVING COUNT(DISTINCT payment_method) > 1


ID
PAYMENT_METHOD


1
CASH

3
CHEQUE


SELECT *
FROM   (
  SELECT id,
         payment_method,
         ROWNUM AS ordering_column
  FROM   table_name t
)
MATCH_RECOGNIZE(
  PARTITION BY id
  ORDER     BY ordering_column
  MEASURES
    FIRST(payment_method) AS payment_method
  PATTERN (^ same+ diff)
  DEFINE
    same AS FIRST(payment_method) = payment_method,
    diff AS diff.payment_method <> same.payment_method
);


ID
PAYMENT_METHOD


1
CASH

3
CHEQUE