如何在Oracle中为单行中的重复ID返回冗余行 [英] How to return a redundant row for duplicate ID in single row in Oracle

问题描述

我有一个Audit类的表，我们根据其他表中的某些触发器存储一些信息.

ID，Changed_Column，OldValue，NewValue将可用. 现在有可能使用相同的ID，因为更改后的列将具有不同的值，所以我希望将其合并为3-4行，并重复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<>小提琴此处

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 data For exmaple,

ID   ChangedColumn OldValue NewValue
1    Name           Bob     Roy
1    Age            26      28  
1    Section        B       C

When 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 value

Expected result is like,

ID   Name               Age           Section     ChangedColumns
1    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 ChangedColumn 
from tab t
group by id,t.col

) 
pivot ( max(val)  for col in('Name','Age','Section'));

db<>fiddle here

这篇关于如何在Oracle中为单行中的重复ID返回冗余行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！