select *
  from (SELECT address_id,
               'Effective_Start_Date' Changed_Attribute,
               to_char(Effective_Start_Date, 'yyyy-mm-dd') current_value,
               to_char(LAG(t.effective_start_date, 1) over(order by t.effective_start_date desc) , 'yyyy-mm-dd') Previous_Value
          FROM AUDIT_DATA t
        union all
        SELECT address_id,
               'Effective_end_Date' Changed_Attribute,
               to_char(t.effective_end_date , 'yyyy-mm-dd') current_value,
               to_char(LAG(t.effective_end_date, 1) over(order by t.effective_end_date desc) , 'yyyy-mm-dd') Previous_Value
          FROM AUDIT_DATA t
        union all
        SELECT address_id,
               'ADDRESS_LINE_1' Changed_Attribute,
               t.address_line_1 current_value,
               LAG(t.address_line_1, 1) over(order by t.address_line_1 desc) Previous_Value
          FROM AUDIT_DATA t
         union all
        SELECT address_id,
               'ADDRESS_LINE_2' Changed_Attribute,
               Nvl(t.address_line_2, ' ') current_value,
               LAG(t.address_line_2, 1) over(order by t.address_line_2) Previous_Value
          FROM AUDIT_DATA  t
          ) r
 where Previous_Value <> current_value;

可以使用行数而不是延迟=

select * from (select address_id     , 
              'Effective_Start_Date'   Changed_Attribute  ,
              cast(Effective_Start_Date as varchar(40))  current_value,
              ROW_NUMBER() over(partition by address_ID order by last_update_date desc) as ROW_NO from fusion.per_addresses_f_ ) Q where q.ROW_NO=1   -- first of each partiotion = Previous_Value
              ....

您可以为每一行生成6行 ADDRESS 比较如下：

WITH CTE AS
(SELECT T.*, 
        ROW_NUMBER() OVER (PARTITION BY ADDRESS_ID ORDER BY EFFECTIVE_START_DATE DESC) AS RN 
   FROM YOUR_TABLE T)
SELECT * FROM
(SELECT TNEW.ADDRESS_ID, 
        DECODE(LVL,1,'Effective_start_Date',2,'Effective_end_Date',3,'ADDRESS_LINE_1',4,'ADDRESS_LINE_2',5,'TOWN') AS CHANGED_aTTRIBUTE,
        DECODE(LVL,1,TOLD.Effective_start_Date,2,TOLD.Effective_end_Date,3,TOLD.ADDRESS_LINE_1,4,TOLD.ADDRESS_LINE_2,5,TOLD.TOWN) AS PREV_VALUE,
        DECODE(LVL,1,TNEW.Effective_start_Date,2,TNEW.Effective_end_Date,3,TNEW.ADDRESS_LINE_1,4,TNEW.ADDRESS_LINE_2,5,TNEW.TOWN) AS CURRENT_VALUE
   FROM CTE TNEW 
   JOIN CTE TOLD ON TNEW.ADDRESS_ID = TOLD.ADDRESS_ID AND TNEW.RN = 1 AND TOLD.RN=2
   cross join (SELECT LEVEL AS LVL FROM DUAL CONNECT BY LEVEL <=5) L)
WHERE PREV_VALUE <> CURRENT_VALUE
   OR (PREV_VALUE IS NULL AND CURRENT_VALUE IS NOT NULL)
   OR (PREV_VALUE IS NOT NULL AND CURRENT_VALUE IS NULL)

使用上次更新日期来决定哪条记录是第一条记录。。。

/*

ADDRESS_ID          EFFECTIVE_START_DATE   EFFECTIVE_END_DATE   ADDRESS_LINE_1  ADDRESS_LINE_2      TOWN        Person_id   LAST_UPDATE_DATE
300000009360612     2020-04-01              4712-12-31          Box 14          ADAM SELLERS ST     Mirror      157         2002-01-01
300000009360612     2000-03-27              2020-03-31          Box 13                              Mirror      157         2002-02-02

* /

With a1 as (
select 
   cast('300000009360612' as varchar2(200)) as address_id
   , cast('2020-04-01' as varchar2(200)) as EFFECTIVE_START_DATE
   , cast('4712-12-31' as varchar2(200)) as EFFECTIVE_END_DATE
   , cast('Box 14' as varchar2(200)) as ADDRESS_LINE_1
   , cast('ADAM SELLERS ST' as varchar2(200)) as ADDRESS_LINE_2
   , cast('Mirror' as varchar2(200)) as TOWN
   , cast(157 as number(10)) as PERSON_ID
   , to_date('2020-01-01', 'YYYY-MM-DD') as LAST_UPDATE_DATE 
from dual
union all
select 
'300000009360612', '2000-03-27', '2020-03-31', 'Box 13', NULL, 'Mirror', 157, to_date('2020-02-02', 'YYYY-MM-DD')
from dual
),
Q0 as (
select
   address_id
   , effective_start_date, effective_end_date
   , address_line_1, address_line_2
   , town, person_id
   , effective_start_date as current_value
   , LAG(effective_start_date, 1, 0) OVER (partition by person_id, address_id ORDER BY last_update_date) Previous_Value
from a1
),
Q1 as (
select 
   t1.address_id as address_id,
   t1.effective_start_date as effective_start_date_t1, t2.effective_start_date as effective_start_date_t2,
   t1.effective_end_date as effective_end_date_t1, t2.effective_end_date as effective_end_date_t2,
   t1.address_line_1 as address_line_1_t1, t2.address_line_1 as address_line_1_t2,
   t1.address_line_2 as address_line_2_t1, t2.address_line_2 as address_line_2_t2,
   t1.town as town_t1, t2.town as town_t2,
   to_char(t1.person_id) as person_id_t1, to_char(t2.person_id) as person_id_t2
   , to_char(t1.last_update_date, 'YYYY-MM-DD') as last_update_date_t1, to_char(t2.last_update_date, 'YYYY-MM-DD') as last_update_date_t2
from a1 t1 inner join a1 t2 on (t1.person_id = t2.person_id and t1.address_id = t2.address_id and t1.LAST_UPDATE_DATE < t2.LAST_UPDATE_DATE)
where 1=1
order by t1.person_id, t1.address_id, t1.last_update_date
)
select 
   ADDRESS_ID
   , COLX
   , VAL1
   , VAL2
   --, case when nvl(VAL1, 'NA') != nvl(VAL2,'NA') THEN 1 ELSE 0 END CHANGED
from q1 
     unpivot (
       (val1, val2)
       for colx in (
            (EFFECTIVE_START_DATE_T1, EFFECTIVE_START_DATE_T2) as 'EFFECTIVE_START_DATE'
          , (EFFECTIVE_END_DATE_T1, EFFECTIVE_END_DATE_T2) as 'EFFECTIVE_END_DATE'
          , (ADDRESS_LINE_1_T1, ADDRESS_LINE_1_T2) as 'ADDRESS_LINE_1'
          , (ADDRESS_LINE_2_T1, ADDRESS_LINE_2_T2) as 'ADDRESS_LINE_2'
          , (TOWN_T1, TOWN_T2) as 'TOWN'
          , (PERSON_ID_T1, PERSON_ID_T2) as 'PERSON_ID'
          , (LAST_UPDATE_DATE_T1, LAST_UPDATE_DATE_T2) as 'LAST_UPDATE_DATE'
       )
     )
where 1=1
and case when nvl(VAL1, 'NA') != nvl(VAL2,'NA') THEN 1 ELSE 0 END != 0
;

给予

ADDRESS_ID      COLX                        VAL1                VAL2
300000009360612 EFFECTIVE_START_DATE        2020-04-01          2000-03-27
300000009360612 EFFECTIVE_END_DATE          4712-12-31          2020-03-31
300000009360612 ADDRESS_LINE_1              Box 14              Box 13
300000009360612 ADDRESS_LINE_2              ADAM SELLERS ST 
300000009360612 LAST_UPDATE_DATE            2020-01-01          2020-02-02

注：val1=以前的值，val2=当前值（基于上次更新日期）