Hi All,
Let's consider the below example in Oracle 11g:
create table table_a (id number, year number, jobs number, other number, creation_date date);
insert into table_a values(10, 2013,10,5,sysdate);
insert into table_a values(10, 2014,20,15,sysdate);
insert into table_a values(10, 2015,30,7,sysdate);
insert into table_a values(20, 2014,20,null,sysdate);
insert into table_a values(20, 2015,30,null,sysdate);
If i run the query:
select * from table_a
pivot (sum(jobs) jobs, sum(other) other for year in(2013,2014,2015))
order by 1
I get the below result(as expected) :
ID CREATION_DATE 2013_JOBS 2013_OTHER 2014_JOBS 2014_OTHER 2015_JOBS 2015_OTHER
---------- ------------- ---------- ---------- ---------- ---------- ---------- ----------
10 27-OCT-16 10 5 20 15 30 7
20 27-OCT-16 20 30
What i want to display is if the measure column is NULL to pick the latest non-null value - e.g. ID = 20, column 2013_JOBS to show 10 instead of null.
I know that with LAST_VALUE function I can achieve this but I can't refer to the column from the pivot result ( e.g. can't query select 2013_jobs from ( <sql query with pivot>).
Any ideas?
Regards,
Alex