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 |