Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

How to remove gaps/null values from set of columns in a row

GVRDec 1 2010 — edited Dec 2 2010
Im trying to implement a solution for removing null value columns from a row.
Basically in below example i have five codes and corresponding id's for that codes.What im trying to achive here is if
i have a null code then i have to move next not null code and id into its new location.
Example:
'A1'cd1,'A2'cd2,null cd3,'A4'cd4,null cd5,'i1'id1,'i2'id2,null id3,'i4' id4,null id5 
So here cd4 and id4 should take positions of cd3 and id3.
Output should look like this
cd1 cd2 cd3 cd4 cd5     id1 id2 id3 id4 id5
A1  A2   A4              i1  i2  i4
Any help would be highly appreciated for below example:

with temp_table as
(select 'A1'cd1,'A2'cd2,null cd3,'A4'cd4,null cd5,'i1'id1,'i2'id2,null id3,'i4' id4,null id5 from dual union all
select 'A11',null,null,'A44','A55','id11',null,null, 'id44','id55' from dual union all
select null,'A111',null,null,'A555',null,'id111',null, null,'id555' from dual union all
select 'A',null,null,'A1111','E55','id11',null,null, 'id111','id1111' from dual )
select * from temp_table;
Edited by: GVR on Dec 1, 2010 8:27 AM
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 30 2010
Added on Dec 1 2010
7 comments
6,269 views