By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tl(
no varchar2(10),
action varchar2(20),
dt DATE
);
insert into tl(no,action,dt)
values ('A01', '轉IT', to_date('2022/1/3 11:42:20', 'YYYY/MM/DD HH24:MI:SS'));
1 rows affected
insert into tl(no,action,dt)
values ('A01', '退回', to_date('2022/1/3 12:46:48', 'YYYY/MM/DD HH24:MI:SS'));
1 rows affected
insert into tl(no,action,dt)
values ('A02', '轉IT', to_date('2022/1/3 12:48:32', 'YYYY/MM/DD HH24:MI:SS'));
1 rows affected
insert into tl(no,action,dt)
values ('A02', '分文', to_date('2022/1/4 09:04:19', 'YYYY/MM/DD HH24:MI:SS'));
1 rows affected
insert into tl(no,action,dt)
values ('A02', '改分', to_date('2022/1/4 09:11:46', 'YYYY/MM/DD HH24:MI:SS'));
1 rows affected
select no
, action
, to_char(dt, 'YYYY-MM-DD HH24:MI:SS') AS dt
from tl;
| NO | ACTION | DT |
|---|---|---|
| A01 | 轉IT | 2022-01-03 11:42:20 |
| A01 | 退回 | 2022-01-03 12:46:48 |
| A02 | 轉IT | 2022-01-03 12:48:32 |
| A02 | 分文 | 2022-01-04 09:04:19 |
| A02 | 改分 | 2022-01-04 09:11:46 |
select no
, listagg(action, ',') within group (order by dt) as actions
from tl
group by no
order by no;
| NO | ACTIONS |
|---|---|
| A01 | 轉IT,退回 |
| A02 | 轉IT,分文,改分 |
select no
, listagg(action || '->' || to_char(dt, 'YYYY-MM-DD HH24:MI:SS'), ',') within group (order by dt) as actions
from tl
group by no
order by no;
| NO | ACTIONS |
|---|---|
| A01 | 轉IT->2022-01-03 11:42:20,退回->2022-01-03 12:46:48 |
| A02 | 轉IT->2022-01-03 12:48:32,分文->2022-01-04 09:04:19,改分->2022-01-04 09:11:46 |