create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, 'n', 4,
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p002', 2, 2, 'n', 4,
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'n', 4,
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p004', 4, 2, 'y', 4,
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p005', 5, 2, 'y', 4,
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p001', 10, 2, 'n', 4,
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p002', 2, 4, 'n', 4,
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'y', 4,
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p004', 4, 2, 'y', 4,
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p005', 5, 2, 'y', 4,
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p001', 10, 2, 'n', 4,
'2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p002', 2, 4, 'n', 4,
'2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'y', 4,
'2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p004', 5, 3, 'n', 4,
'2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p005', 5, 3, 'y', 8,
'2019-02-13');
select *
from (
select a,
idate,
'b' as variable,
lag(b, 1) over (partition by a order by idate) as oldv,
b as newv
from t
union
select a,
idate,
'c' as variable,
lag(c, 1) over (partition by a order by idate) as oldv,
c as newv
from t
union
select a,
idate,
'd' as variable,
lag(d, 1) over (partition by a order by idate) as oldv,
d as newv
from t
union
select a,
idate,
'e' as variable,
lag(e, 1) over (partition by a order by idate) as oldv,
e as newv
from t
)
where oldv <> newv;
a idate variable oldv newv
---------- ---------- ---------- ---------- ----------
p001 2019-02-12 b 1 10
p002 2019-02-12 c 2 4
p003 2019-02-12 d n y
p004 2019-02-13 b 4 5
p004 2019-02-13 c 2 3
p004 2019-02-13 d y n
p005 2019-02-13 c 2 3
p005 2019-02-13 e 4 8
Change "<>" to "IS NOT" to find out when their was no previous value