create table t1 
     (
     id1 varchar2(10),
     id2 varchar2(10),
     id3 varchar2(10),
     id4 varchar2(10)
     )

 insert into t1 values (1,null,null,null);
     insert into t1 values (null,'A',null,null);
     insert into t1 values (null,null,'Tom',null);
     insert into t1 values (null,null,null,32);
     insert into t1 values   (2,null,null,null);
     insert into t1 values (null,'B',null,null);
     insert into t1 values (null,null,'Henry',null);
     insert into t1 values (1,null,null,36);

ID1	ID2	ID3	ID4
1	A	Tom	32
2	B 	Henry 	36

SQL> col id1 format a10
SQL> col id2 format a10
SQL> col id3 format a10
SQL> col id4 format a10
SQL> with 
  2    data as (
  3      select xmltype('<records><record>
  4  <id1>1</id1>
  5  <id2>null</id2>
  6  <id3>null</id3>
  7  <id4>null</id4>
  8  </record>
  9  <record>
 10  <id1>null</id1>
 11  <id2>A</id2>
 12  <id3>null</id3>
 13  <id4>null</id4>
 14  </record>
 15  <record>
 16  <id1>null</id1>
 17  <id2>null</id2>
 18  <id3>Tom</id3>
 19  <id4>null</id4>
 20  </record>
 21  <record>
 22  <id1>null</id1>
 23  <id2>null</id2>
 24  <id3>null</id3>
 25  <id4>32</id4>
 26  </record>
 27  </records>') val from dual
 28    )
 29  select max(nullif(extractvalue(value(x), '//id1'),'null')) id1,
 30         max(nullif(extractvalue(value(x), '//id2'),'null')) id2,
 31         max(nullif(extractvalue(value(x), '//id3'),'null')) id3,
 32         max(nullif(extractvalue(value(x), '//id4'),'null')) id4
 33  from data,
 34       table(xmlsequence(extract(val, '//record'))) x
 35  /
ID1        ID2        ID3        ID4
---------- ---------- ---------- ----------
1          A          Tom        32

SQL> insert into t values (xmltype('<records><record>
  2  <id1>1</id1>
  3  <id2>null</id2>
  4  <id3>null</id3>
  5  <id4>null</id4>
  6  </record>
  7  <record>
  8  <id1>null</id1>
  9  <id2>A</id2>
 10  <id3>null</id3>
 11  <id4>null</id4>
 12  </record>
 13  <record>
 14  <id1>null</id1>
 15  <id2>null</id2>
 16  <id3>Tom</id3>
 17  <id4>null</id4>
 18  </record>
 19  <record>
 20  <id1>null</id1>
 21  <id2>null</id2>
 22  <id3>null</id3>
 23  <id4>32</id4>
 24  </record>
 25  </records>')) ;

1 row created.

SQL> select max(nullif(extractvalue(value(x), '//id1'),'null')) id1,
  2         max(nullif(extractvalue(value(x), '//id2'),'null')) id2,
  3         max(nullif(extractvalue(value(x), '//id3'),'null')) id3,
  4         max(nullif(extractvalue(value(x), '//id4'),'null')) id4
  5  from t,
  6       table(xmlsequence(extract(val, '//record'))) x
  7  /
ID1        ID2        ID3        ID4
---------- ---------- ---------- ----------
1          A          Tom        32

1 row selected.

select  (select max(id1) from t1 where ascii(substr(rowid,18,1)) between 65 and 68) as id1, a,b,c
from(
select  max(case when r >= 65 and r <= 68 then id1 end) as id1,
        max(id2) a,
        max(id3) b,
        max(nvl(id4,0)) c
from    
(
    select  ascii(substr(rowid,18,1)) as r,         
            id1,
            id2,
            id3,
            id4 
    from    t1
)
where r between 65 and 68
-----group by case when r >= 69 and r <= 72 then id1 end
)
where a is not null
union all
select  (select max(id1) from t1 where ascii(substr(rowid,18,1)) between 69 and 72) as id1, a,b,c
from(
select  max(case when r >= 69 and r <= 72 then id1 end) as id1,
        max(id2) a,
        max(id3) b,
        max(nvl(id4,0)) c
from    
(
    select  ascii(substr(rowid,18,1)) as r,         
            id1,
            id2,
            id3,
            id4 
    from    t1
)
where r between 69 and 72
-----group by case when r >= 69 and r <= 72 then id1 end
)
where a is not null

o/p :

ID1	A	B	C

1	A	Tom	32
2	B	Henry	36

ascii(substr(rowid,18,1))