with  x as (select '1000257' as ASSET_NUMBER,'GOOD' AS OBSERVATION,'CBMO_ASSESSMENT' AS METER_NAME from dual
UNION ALL
SELECT '1010257' AS ASSET_NUMBER,'BAD' AS OBSERVATION, 'SF6B_ASSESSMENT' as METER_NAME from dual
)
select ASSET_NUMBER, METER_NAME_1_OBSERVATION,METER_NAME_2_OBSERVATION from x
PIVOT(
       MIN(OBSERVATION) AS OBSERVATION
    for METER_NAME in 
    ( 
      'CBMO_ASSESSMENT'  AS METER_NAME_1 , 'SF6B_ASSESSMENT' AS METER_NAME_2
    )
  )
  
It is returning output as shown below

ASSET_NUMBER   METER_NAME_1_OBSERVATION    METER_NAME_2_OBSERVATION
1000257              GOOD                           NULL
1000257              NULL                           BAD

ASSET_NUMBER METER_NAME_1_OBSERVATION    METER_NAME_2_OBSERVATION
1000257      GOOD                         BAD

orclz> with  x as (select '1000257' as ASSET_NUMBER,'GOOD' AS OBSERVATION,'CBMO_ASSESSMENT' AS METER_NAME from dual
  2  UNION ALL
  3  SELECT '1010257' AS ASSET_NUMBER,'BAD' AS OBSERVATION, 'SF6B_ASSESSMENT' as METER_NAME from dual
  4  )
  5  select * from x;

ASSET_N OBSE METER_NAME
------- ---- ---------------
1000257 GOOD CBMO_ASSESSMENT
1010257 BAD  SF6B_ASSESSMENT

orclz>

WITH  X AS (
            SELECT '1000257' AS ASSET_NUMBER,'GOOD' AS OBSERVATION,'CBMO_ASSESSMENT' AS METER_NAME FROM DUAL UNION ALL
            SELECT '1000257' AS ASSET_NUMBER,'BAD' AS OBSERVATION, 'SF6B_ASSESSMENT' AS METER_NAME FROM DUAL
           )
SELECT  *
  FROM  X
  PIVOT(
        MAX(OBSERVATION)
        FOR METER_NAME IN (
                           'CBMO_ASSESSMENT' METER_NAME_1_OBSERVATION,
                           'SF6B_ASSESSMENT' METER_NAME_2_OBSERVATION
                          )
       )
/

ASSET_NUMBER METER_NAME_1_OBSERVATION  METER_NAME_2_OBSERVATION
------------ ------------------------- -------------------------
1000257      GOOD                      BAD

SQL>