How to covert the multiple row to one row and multiple column in oracle

How to covert the multiple row to one row and multiple column in oracle:
–Sample Data–

–Expected Result—

>Solution :

One option is conditional aggregation.

Sample data:

SQL> with test (key_id, block_name, field_name, old_value, new_value) as
  2    (select 297, 'STTMS_CUSTOMER', 'MARKER_ID', '0070KLE', '0364MHA' from dual union all
  3     select 297, 'STTMS_CUSTOMER', 'MARKER_DT_STAMP', '09-SEP-19', '23-MAR-22' from dual union all
  4     select 297, 'STTMS_CUSTOMER', 'CHECKER_ID','0063CLU', null from dual union all
  5     select 297, 'STTMS_CUSTOMER', 'CHECKER_DT_STAMP', '09-SEP-19', null from dual
  6    )

Query:

  7  select key_id, block_name,
  8    --
  9    max(case when field_name = 'MARKER_ID' then old_value end) marker_id_old,
 10    max(case when field_name = 'MARKER_ID' then new_value end) marker_id_new,
 11    --
 12    max(case when field_name = 'MARKER_DT_STAMP' then old_value end) marker_dts_old,
 13    max(case when field_name = 'MARKER_DT_STAMP' then new_value end) marker_dts_new,
 14    --
 15    max(case when field_name = 'CHECKER_ID' then old_value end) checker_id_old,
 16    max(case when field_name = 'CHECKER_ID' then new_value end) checker_id_new,
 17    --
 18    max(case when field_name = 'CHECKER_DT_STAMP' then old_value end) marker_dts_old,
 19    max(case when field_name = 'CHECKER_DT_STAMP' then new_value end) marker_dts_new
 20  from test
 21  group by key_id, block_name;

    KEY_ID BLOCK_NAME     MARKER_ID MARKER_ID MARKER_DT MARKER_DT CHECKER_I CHECKER_I MARKER_DT MARKER_DT
---------- -------------- --------- --------- --------- --------- --------- --------- --------- ---------
       297 STTMS_CUSTOMER 0070KLE   0364MHA   09-SEP-19 23-MAR-22 0063CLU             09-SEP-19

SQL>