您可以使用cross apply取消透视，然后聚合结果集以检索每行的最新操作日期，同时keep执行相应的用户。

insert into newtable (dt_id, action_user, action_date_time)
select d.dt_id,
    max(x.action_user) keep(dense_rank last order by x.action_date_time),
    max(x.action_date_time)
from datatable d
cross apply (
    select d.assigned_date_time action_date_time, d.assigned_by action_user from dual
    union all select d.accepted_date_time, d.accepted_by from dual
    union all select d.rejected_date_time, d.rejected_by from dual
) x
group by d.dt_id

使用GREATEST和CASE表达式：

INSERT INTO newTable(id, action_user, action_date)
SELECT DT_ID,
       CASE GREATEST(assigned_date_time, accepted_date_time, rejected_date_time)
       WHEN rejected_date_time
       THEN rejected_by
       WHEN accepted_date_time
       THEN accepted_by
       WHEN assigned_date_time
       THEN assigned_by
       END,
       GREATEST(assigned_date_time, accepted_date_time, rejected_date_time)
FROM   datatable;

其中，对于样本数据：

CREATE TABLE datatable
(
   "DT_ID"                NUMBER (10, 0),
   "ASSIGNED"             NUMBER (1, 0) DEFAULT 0,
   "ASSIGNED_DATE_TIME"   DATE,
   "ASSIGNED_BY"          VARCHAR2 (7 BYTE),
   "ACCEPTED"             NUMBER (1, 0) DEFAULT 0,
   "ACCEPTED_DATE_TIME"   DATE,
   "ACCEPTED_BY"          VARCHAR2 (7 BYTE),
   "REJECTED"             NUMBER (1, 0) DEFAULT 0,
   "REJECTED_DATE_TIME"   DATE,
   "REJECTED_BY"          VARCHAR2 (7 BYTE)
);

INSERT INTO dataTable
SELECT 1, 0, DATE '2023-01-01', 'Alice', 1, DATE '2023-02-01', 'Betty', 2, DATE '2023-03-01', 'Carol' FROM DUAL;

CREATE TABLE newTable(
  id          NUMBER(10,0),
  action_user VARCHAR2(7 BYTE),
  action_date DATE
);

然后，在插入之后，该表包含：
| ID|操作_用户|行动日期|
| --------------|--------------|--------------|
| 1|卡罗尔|2019 -03-01 00：00：00|
fiddle

更新：

如果列中可以有NULL值，那么也可以使用COALESCE，并将这些值合并为一个日期文本，该日期文本保证早于表中的任何其他值：

INSERT INTO newTable(id, action_user, action_date )
SELECT DT_ID,
       CASE GREATEST(
              COALESCE(assigned_date_time, DATE '1900-01-01'),
              COALESCE(accepted_date_time, DATE '1900-01-01'),
              COALESCE(rejected_date_time, DATE '1900-01-01')
            )
       WHEN COALESCE(rejected_date_time, DATE '1900-01-01')
       THEN rejected_by
       WHEN COALESCE(accepted_date_time, DATE '1900-01-01')
       THEN accepted_by
       WHEN COALESCE(assigned_date_time, DATE '1900-01-01')
       THEN assigned_by
       END,
       CASE GREATEST(
              COALESCE(assigned_date_time, DATE '1900-01-01'),
              COALESCE(accepted_date_time, DATE '1900-01-01'),
              COALESCE(rejected_date_time, DATE '1900-01-01')
            )
       WHEN COALESCE(rejected_date_time, DATE '1900-01-01')
       THEN rejected_date_time
       WHEN COALESCE(accepted_date_time, DATE '1900-01-01')
       THEN accepted_date_time
       WHEN COALESCE(assigned_date_time, DATE '1900-01-01')
       THEN assigned_date_time
       END
FROM   datatable;

其中，对于样本数据：

CREATE TABLE datatable
(
   "DT_ID"                NUMBER (10, 0),
   "ASSIGNED"             NUMBER (1, 0) DEFAULT 0,
   "ASSIGNED_DATE_TIME"   DATE,
   "ASSIGNED_BY"          VARCHAR2 (7 BYTE),
   "ACCEPTED"             NUMBER (1, 0) DEFAULT 0,
   "ACCEPTED_DATE_TIME"   DATE,
   "ACCEPTED_BY"          VARCHAR2 (7 BYTE),
   "REJECTED"             NUMBER (1, 0) DEFAULT 0,
   "REJECTED_DATE_TIME"   DATE,
   "REJECTED_BY"          VARCHAR2 (7 BYTE)
);

INSERT INTO dataTable
SELECT 1, 0, DATE '2023-01-01', 'Alice', 1, DATE '2023-02-01', 'Betty', 2, DATE '2023-03-01', 'Carol' FROM DUAL UNION ALL
SELECT 1, 0, DATE '2023-03-01', 'Debra', NULL, NULL, NULL, 2, DATE '2023-01-01', 'Emily' FROM DUAL

CREATE TABLE newTable(
  id          NUMBER(10,0),
  action_user VARCHAR2(7 BYTE),
  action_date DATE
);

然后，在插入之后，该表包含：
| ID|操作_用户|行动日期|
| --------------|--------------|--------------|
| 1|卡罗尔|2019 -03-01 00：00：00|
| 1|黛布拉|2019 -03-01 00：00：00|
fiddle