Hello,
I would like to get the position changed from left to right based on the NON NULL values. If any of the column has null, the next field should shifted to one position up etc..
drop table test;
create table test (id number, sal1 number, sal2 number, sal3 number, sal4 number, sal5 number);
insert into test values (1, 10, null, null, 20,30);
insert into test values (2, null, null, 20,30,40);
insert into test values (3, 10, 20, null, null, 30);
insert into test values (4, null, null, null, null, 50);
insert into test values (5, null, null, null, null, null);
insert into test values (6, 1, 2, 3, 4, 5);
commit;
select * from test;
id sal1 sal2 sal3 sal4 sal5
1 10 20 30
2 20 30 40
3 10 20 30
4 50
5
6 1 2 3 4 5
select id,
case when sal1 is not null then sal1
else coalesce (sal2,sal3,sal4,sal5)
end as shift_sal_1,
case when sal1 is null and sal2 is NOT null then sal2
else coalesce (sal2,sal3,sal4,sal5)
end as shift_sal_2,
case when sal1 is null and sal2 is null and sal3 is not null then sal3
else coalesce (sal4,sal5)
end as shift_sal_3
from test;
My expected output should be like below
==================================
ID, shift_sal_1, shift_sal_2, shift_sal_3, shift_sal_4, shift_sal_5
1 10 20 30
2 20 30 40
3 10 20 30
4 50
5
6 1 2 3 4 5