col emid for a4;
col 部门_经办日期 for a14;
col 职位_经办日期 for a14;
col 工资_经办日期 for a14;
col 部门_旧值 for a10;
col 职位_旧值 for a10;
col 工资_旧值 for a10;
col 工资_新值 for a10;
col 部门_新值 for a10;
col 职位_新值 for a14;
select * from TAB1 pivot(
LISTAGG(to_char(pfdt,'yyyy-mm-dd') ,chr(10)) WITHIN GROUP (ORDER BY pfdt) "经办日期",
LISTAGG(ov,chr(10)) WITHIN GROUP (ORDER BY pfdt) "旧值",
LISTAGG(nv ,chr(10)) WITHIN GROUP (ORDER BY pfdt) "新值" for TP in('部门' as "部门" ,'工资' as "工资",'职位' as "职位")) order by 1;

EMID 部门_经办日期 部门_旧值 部门_新值 工资_经办日期 工资_旧值 工资_新值 职位_经办日期 职位_旧值 职位_新值
---- -------------- ---------- ---------- -------------- ---------- ---------- -------------- ---------- --------------
A 2024-01-01 D1 D2 2023-10-01 20000 22000
2024-03-01 22000 25000

B 2024-01-01 D2 D3 2024-02-01 软件工程师 高级软件工程师
C 2023-11-01 18000 23000

select coalesce(a.emid,b.emid,c.emid) as emid
,a.pfdt as d_pfdt
,a.ov as d_ov
,a.nv as d_nv
,b.pfdt as s_pfdt
,b.ov as s_ov
,b.nv as s_nv
,c.pfdt as p_pfdt
,c.ov as p_ov
,c.nv as p_nv
from
(
select emid,pfdt,ov,nv,row_number() over(partition by emid order by pfdt) as sn
from v_log
where tp = '部门'
) a
full join
(
select emid,pfdt,ov,nv,row_number() over(partition by emid order by pfdt) as sn
from v_log
where tp = '工资'
) b
on a.emid = b.emid
and a.sn = b.sn
full join
(
select emid,pfdt,ov,nv,row_number() over(partition by emid order by pfdt) as sn
from v_log
where tp = '职位'
) c
on a.emid = c.emid
and a.sn = c.sn
;

SQL没实际跑过，可能需要调下。 大致思路就这样。