Oracle 21c

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

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE transaction (ID, status) AS
  SELECT 1000, 'C' FROM DUAL UNION ALL
  SELECT 1231, 'C' FROM DUAL UNION ALL
  SELECT 1231, 'C' FROM DUAL UNION ALL
  SELECT 1231, 'S' FROM DUAL UNION ALL
  SELECT 1231, 'S' FROM DUAL UNION ALL
  SELECT 1231, 'S' FROM DUAL UNION ALL
  SELECT 1231, 'E' FROM DUAL;
7 rows affected

SELECT ID,
       LISTAGG(status || ':' || status_count, ', ')
         WITHIN GROUP (ORDER BY status) AS results
FROM   (
  SELECT ID,
         STATUS,
         COUNT(STATUS) AS status_count
  FROM   TRANSACTION
  GROUP BY
         ID,
         STATUS
)
GROUP BY
       ID;


ID
RESULTS


1000
C:1

1231
C:2, E:1, S:3