您可以检查添加了PROJECTION信息的执行计划，这将显示数据库在每个步骤中请求了哪些表达式。

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

个字符
正如您所看到的，HASH GROUP BY操作（计划行= 1）请求三个表达式：一个常规列和两个PIVOT艾德列（这也显示了如何在没有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列对于这两行是不同的，但是只要它没有被投影，输出就可能会混淆。然而，这是有记录的：
相反，pivot_clause执行隐式GROUP BY。隐式分组基于pivot_clause中未引用的所有列沿着pivot_in_clause中指定的一组值。
为了得到你想要的，你可以：

• 使用子查询减少PIVOT的输入列，只保留必需的列。

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

型

• 通过aggregate_function(CASE WHEN pivot_in_col = 'EXPR' THEN pivot_for_col END)使用显式GROUP BY手动构建PIVOT表达式。

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

型
对于您的示例数据，两者都将返回相同的结果：
| METER_NAME_2_观察| METER_NAME_2_OBSERVATION |
| --| ------------ |
| 坏| BAD |
fiddle

这是因为ASSET_NUMBER的值不同（1000257和1010257）。
如果它们是一样的，你就会得到你想要的：

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>

字符串
另一个选项（条件聚合）：

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

型