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', '2PM', 'PT' from dual union all
select 1, 'C', '3PM', 'PT' from dual union all
select 2, 'D', '2PM', 'FR' from dual union all
select 2, 'E', '1PM', 'GB' 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 | 2PM | PT | 3 |
| 1 | C | 3PM | PT | 3 |
| 2 | D | 2PM | FR | 2 |
| 2 | E | 1PM | GB | 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,
listagg(time, ',') within group (order by time) as times,
listagg(zone, ',') within group (order by zone) as zones
from (
select id, message, time, zone, count(*) over (partition by id) as cnt
from your_table
)
where cnt < 3
group by id
order by id;
| ID | MESSAGES | TIMES | ZONES |
|---|---|---|---|
| 2 | D,E | 1PM,2PM | FR,GB |
| 3 | F | 3PM | TK |
select id,
listagg(case when message = 'X' then 'Z' else message end, ',')
within group (order by message) as messages,
listagg(time || zone, ',') within group (order by time, zone) as times
from (
select id, message, time, zone, count(*) over (partition by id) as cnt
from your_table
)
where cnt < 3
group by id
order by id;
| ID | MESSAGES | TIMES |
|---|---|---|
| 2 | D,E | 1PMGB,2PMFR |
| 3 | F | 3PMTK |