如何在Oracle中为单行中的重复ID返回冗余行

I have a Audit kind of table where we store some information based on some triggers in other tables.ID, Changed_Column, OldValue, NewValue will be available.Now there is a possibility for same Id there will be 3-4 duplicates since the changed column will have different values I wants to merge them into single row and take dataFor exmaple,ID ChangedColumn OldValue NewValue1 Name Bob Roy1 Age 26 281 Section B CWhen we select now it will display all the rows into separte but I wants to self join and retrieve only one record by merging based on ID valueExpected result is like, ID Name Age Section ChangedColumns1 was :Bob now : Roy was:26 now:28 Was:B now:C Name, Age, Section 解决方案 to Group the column names you can use listagg function.to convert rows to columns use Pivot function.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 ChangedColumnfrom tab tgroup by id,t.col)pivot ( max(val) for col in('Name','Age','Section'));db<>fiddle here 这篇关于如何在Oracle中为单行中的重复ID返回冗余行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！