Another version that doesn't use CTE nor window functions:
select t1.a as Proj, t2.idate as "On", 'b' as Var, t1.b as oldVal, t2.b
as newVal
from t t1,t t2
where t1.a = t2.a
and t2.idate = date(t1.idate,'+1 day')
and t1.b <> t2.b
union all
select t1.a as Proj, t2.idate as "On", 'c' as Var, t1.c as oldVal, t2.c
as newVal
from t t1,t t2
where t1.a = t2.a
and t2.idate = date(t1.idate,'+1 day')
and t1.c <> t2.c
union all
select t1.a as Proj, t2.idate as "On", 'd' as Var, t1.d as oldVal, t2.d
as newVal
from t t1,t t2
where t1.a = t2.a
and t2.idate = date(t1.idate,'+1 day')
and t1.d <> t2.d
union all
select t1.a as Proj, t2.idate as "On", 'e' as Var, t1.e as oldVal, t2.e
as newVal
from t t1,t t2
where t1.a = t2.a
and t2.idate = date(t1.idate,'+1 day')
and t1.e <> t2.e
order by Proj,"On";
Valid if there is one state for each project on each date.
Jean-Luc Hainaut
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
