我有一个审计类型的表,我们根据其他表中的一些触发器存储一些信息。
ID,Changed_Column,OldValue,NewValue将可用。现在有可能使用相同的 Id,会有 3-4 个重复项,因为更改的列将具有不同的值我想将它们合并到单行中并获取数据例如,
ID ChangedColumn OldValue NewValue
1 Name Bob Roy
1 Age 26 28
1 Section B C
当我们现在选择时,它将把所有行都显示为分离,但我想通过根据 ID 值合并来自我连接并仅检索一条记录
预期结果是这样的,
ID Name Age Section ChangedColumns
1 was :Bob now : Roy was:26 now:28 Was:B now:C Name, Age, Section
对可以使用listagg
函数的列名进行分组。要将行转换为列,请使用Pivot
函数。
with tab as(
select 1 as id, 'Name' as col, 'Bob' as OldValue , 'Roy' as NewValue from dual union all
select 1 as id, 'Age', '26', '28' as NewValue from dual union all
select 1 as id, 'Section', 'B', 'C' as NewValue from dual
)
select *
from (
select id
,t.col as col
,max('was: '|| t.OldValue || ' now: ' || t.NewValue) as val
,listagg(t.col,',') within group(order by t.id) OVER (PARTITION BY null) as ChangedColumn
from tab t
group by id,t.col
)
pivot ( max(val) for col in('Name','Age','Section'));
db<>在这里小提琴
使用条件聚合似乎很简单:
select id,
max(case when col = 'Name' then str end) as name,
max(case when col = 'Age' then str end) as age,
max(case when col = 'Section' then str end) as section
from (select t.*, ('was: ' || OldValue || ' now: ' || NewValue) as str
from t
) t
group by id;
这是一个数据库<>小提琴。