By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table your_table (id, message, time, zone) as
select 1, 'A', '1PM', 'PT' from dual union all
select 1, 'B', '1PM', 'PT' from dual union all
select 1, 'C', '1PM', 'PT' from dual union all
select 2, 'D', '2PM', 'FR' from dual union all
select 2, 'E', '2PM', 'FR' from dual union all
select 3, 'F', '3PM', 'TK' from dual
6 rows affected
select id, message, time, zone, count(*) over (partition by id) as cnt
from your_table
| ID | MESSAGE | TIME | ZONE | CNT |
|---|---|---|---|---|
| 1 | A | 1PM | PT | 3 |
| 1 | B | 1PM | PT | 3 |
| 1 | C | 1PM | PT | 3 |
| 2 | D | 2PM | FR | 2 |
| 2 | E | 2PM | FR | 2 |
| 3 | F | 3PM | TK | 1 |
select id,
listagg(case when message = 'X' then 'Z' else message end, ',')
within group (order by message) as messages,
time,
zone
from (
select id, message, time, zone, count(*) over (partition by id) as cnt
from your_table
)
where cnt < 3
group by id, time, zone
order by id, time, zone;
| ID | MESSAGES | TIME | ZONE |
|---|---|---|---|
| 2 | D,E | 2PM | FR |
| 3 | F | 3PM | TK |