PIVOT on TEXT Columns

The below is my SQL as shown below. I wanted to pivot OBSERVATION meter wise into different column. As OBSERVATION is text field Hence if I write MIN(OBSERVATION) it returns as a null.

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 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
    )
  )
    
    

enter image description here

I wanted output as shown below
enter image description here

How can I achieve this as I am not able to PIVOT MIN(OBSERVATION) on a text field.

I wanted this to be added in 1 line instead of 2 lines as for the same asset. I wanted this to only show in 1 line instead of 2 lines.

Appreciate your help on the below.

KInd Regards,

I wanted to pivot a text field into a different column but returning as a null record . How can i achieve this sir as it is returning a null record.

Appreciate your help sir ?

You may check the execution plan with the PROJECTION information added, which will show you what expressions were requested by the database at each step.

select *
from dbms_xplan.display_cursor(
  format => '+PROJECTION +ALIAS'
)

 PLAN_TABLE_OUTPUT 
 :-----------------
 SQL_ID  804p20bcdahaj, child number 0 
 ------------------------------------- 
 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 METER_NAME_1_OBSERVATION,METER_NAME_2_OBSE 
 RVATION from x PIVOT(        MIN(OBSERVATION) AS OBSERVATION     for  
 METER_NAME in      (        'CBMO_ASSESSMENT'  AS METER_NAME_1 ,  
 'SF6B_ASSESSMENT' AS METER_NAME_2     )   ) 
   
 Plan hash value: 3479842717 
   
 ---------------------------------------------------------------------------- 
 | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
 ---------------------------------------------------------------------------- 
 |   0 | SELECT STATEMENT    |      |       |       |     5 (100)|          | 
 |   1 |  HASH GROUP BY PIVOT|      |     2 |    60 |     5  (20)| 00:00:01 | 
 |   2 |   VIEW              |      |     2 |    60 |     4   (0)| 00:00:01 | 
 |   3 |    UNION-ALL        |      |     2 |       |     4   (0)| 00:00:01 | 
 |   4 |     FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 | 
 |   5 |     FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 | 
 ---------------------------------------------------------------------------- 
   
 Query Block Name / Object Alias (identified by operation id): 
 ------------------------------------------------------------- 
   
    1 - SEL$16C51A37 
    2 - SET$1        / "X"@"SEL$1" 
    3 - SET$1        
    4 - SEL$2        / "DUAL"@"SEL$2" 
    5 - SEL$3        / "DUAL"@"SEL$3" 
   
 Column Projection Information (identified by operation id): 
 ----------------------------------------------------------- 
   
    1 - (#keys=1) "X"."ASSET_NUMBER"[CHARACTER,7], MIN(CASE  WHEN  
        (INTERNAL_FUNCTION("METER_NAME")='CBMO_ASSESSMENT') THEN "OBSERVATION"  
        END )[4], MIN(CASE  WHEN (INTERNAL_FUNCTION("METER_NAME")='SF6B_ASSESSME 
        NT') THEN "OBSERVATION" END )[4] 
    2 - "X"."ASSET_NUMBER"[CHARACTER,7], "OBSERVATION"[VARCHAR2,4],  
        "METER_NAME"[CHARACTER,15] 
    3 - STRDEF[7], STRDEF[4], STRDEF[15] 
   

As you may see, HASH GROUP BY operation (plan line = 1) requests three expressions: one regular column and two PIVOTed columns (which also shows how to do it without PIVOT).

• "X"."ASSET_NUMBER"
• MIN(CASE WHEN (INTERNAL_FUNCTION("METER_NAME")='CBMO_ASSESSMENT') THEN "OBSERVATION" END)
• MIN(CASE WHEN (INTERNAL_FUNCTION("METER_NAME")='SF6B_ASSESSMENT') THEN "OBSERVATION" END)

ASSET_NUMBER column is different for these two rows, but as long as it is not projected, the output may be confusing. However, this is documented:
> Instead, the pivot_clause performs an implicit GROUP BY. The implicit grouping is based on all the columns not referred to in the pivot_clause, along with the set of values specified in the pivot_in_clause.

So to get what you want, you may either:

• Use subquery to reduce input columns for PIVOT and keep only required ones.

select meter_name_1_observation,meter_name_2_observation
from (
  select observation, meter_name
  from x
) x
pivot(
  min(observation) as observation
  for meter_name in ( 
    'CBMO_ASSESSMENT'  as meter_name_1 , 'SF6B_ASSESSMENT' as meter_name_2
  )
)

• Build PIVOT expressions manually via aggregate_function(CASE WHEN pivot_in_col = 'EXPR' THEN pivot_for_col END) with explicit GROUP BY.

select
  min(case meter_name when 'CBMO_ASSESSMENT' then observation end) as meter_name_1_observation,
  min(case meter_name when 'SF6B_ASSESSMENT' then observation end) as meter_name_2_observation
from x

Both will return the same result for your sample data:

fiddle

That's because ASSET_NUMBER values are different (1000257 and 1010257).

If they were the same, you'd get what you want:

SQL> WITH
  2     x
  3     AS
  4        (SELECT '1010257' AS asset_number,                --> same ...
  5                'GOOD' AS observation,
  6                'CBMO_ASSESSMENT' AS meter_name
  7           FROM DUAL
  8         UNION ALL
  9         SELECT '1010257' AS asset_number,                --> ... ASSET_NUMBER values!
 10                'BAD' AS observation,
 11                'SF6B_ASSESSMENT' AS meter_name
 12           FROM DUAL)
 13  SELECT asset_number, METER_NAME_1_OBSERVATION, METER_NAME_2_OBSERVATION
 14    FROM x
 15         PIVOT (MIN (OBSERVATION) AS OBSERVATION
 16               FOR METER_NAME
 17               IN ('CBMO_ASSESSMENT' AS METER_NAME_1, 'SF6B_ASSESSMENT' AS METER_NAME_2));

ASSET_N METE METE
------- ---- ----
1010257 GOOD BAD

SQL>

Another option (conditional aggregation):

 <snip>
 13    SELECT asset_number,
 14           MAX (CASE WHEN meter_name = 'CBMO_ASSESSMENT' THEN observation END) meter_1,
 15           MAX (CASE WHEN meter_name = 'SF6B_ASSESSMENT' THEN observation END) meter_2
 16      FROM x
 17  GROUP BY asset_number;

ASSET_N METE METE
------- ---- ----
1010257 GOOD BAD

SQL>