Oracle中如何将多行数据合并为一行？附示例与预期结果

原查询语句如下：

SELECT 
   AC.TRN_REF_NO
  ,AC.EVENT
  ,AC.AC_NO
  ,AC.AC_CCY
  ,AC.FCY_AMOUNT
  ,AC.EXCH_RATE
  ,AC.LCY_AMOUNT
  ,AC.RELATED_CUSTOMER
  ,AC.TRN_CODE
FROM ACVW_ALL_AC_ENTRIES AC 
WHERE AC.TRN_REF_NO = '001INFT233140130';

针对该查询返回的4行数据，要合并为1行，以下是两种Oracle中的实现方法：

方法一：CASE WHEN 结合聚合函数 #

通过CASE WHEN区分不同行的标识（比如EVENT或TRN_CODE），再用MAX()/MIN()聚合函数提取对应字段值，最终合并为单行：

SELECT 
   AC.TRN_REF_NO
   -- 提取第一行（替换为实际EVENT值）的字段
  ,MAX(CASE WHEN AC.EVENT = 'EVENT_VAL1' THEN AC.AC_NO END) AS AC_NO_1
  ,MAX(CASE WHEN AC.EVENT = 'EVENT_VAL1' THEN AC.AC_CCY END) AS AC_CCY_1
  ,MAX(CASE WHEN AC.EVENT = 'EVENT_VAL1' THEN AC.FCY_AMOUNT END) AS FCY_AMOUNT_1
  ,MAX(CASE WHEN AC.EVENT = 'EVENT_VAL1' THEN AC.EXCH_RATE END) AS EXCH_RATE_1
  ,MAX(CASE WHEN AC.EVENT = 'EVENT_VAL1' THEN AC.LCY_AMOUNT END) AS LCY_AMOUNT_1
  -- 提取第二行的字段
  ,MAX(CASE WHEN AC.EVENT = 'EVENT_VAL2' THEN AC.AC_NO END) AS AC_NO_2
  ,MAX(CASE WHEN AC.EVENT = 'EVENT_VAL2' THEN AC.AC_CCY END) AS AC_CCY_2
  ,MAX(CASE WHEN AC.EVENT = 'EVENT_VAL2' THEN AC.FCY_AMOUNT END) AS FCY_AMOUNT_2
  ,MAX(CASE WHEN AC.EVENT = 'EVENT_VAL2' THEN AC.EXCH_RATE END) AS EXCH_RATE_2
  ,MAX(CASE WHEN AC.EVENT = 'EVENT_VAL2' THEN AC.LCY_AMOUNT END) AS LCY_AMOUNT_2
  -- 补充第三、第四行的对应字段
  ,MAX(CASE WHEN AC.EVENT = 'EVENT_VAL3' THEN AC.AC_NO END) AS AC_NO_3
  ,MAX(CASE WHEN AC.EVENT = 'EVENT_VAL3' THEN AC.AC_CCY END) AS AC_CCY_3
  ,MAX(CASE WHEN AC.EVENT = 'EVENT_VAL3' THEN AC.FCY_AMOUNT END) AS FCY_AMOUNT_3
  ,MAX(CASE WHEN AC.EVENT = 'EVENT_VAL3' THEN AC.EXCH_RATE END) AS EXCH_RATE_3
  ,MAX(CASE WHEN AC.EVENT = 'EVENT_VAL3' THEN AC.LCY_AMOUNT END) AS LCY_AMOUNT_3
  ,MAX(CASE WHEN AC.EVENT = 'EVENT_VAL4' THEN AC.AC_NO END) AS AC_NO_4
  ,MAX(CASE WHEN AC.EVENT = 'EVENT_VAL4' THEN AC.AC_CCY END) AS AC_CCY_4
  ,MAX(CASE WHEN AC.EVENT = 'EVENT_VAL4' THEN AC.FCY_AMOUNT END) AS FCY_AMOUNT_4
  ,MAX(CASE WHEN AC.EVENT = 'EVENT_VAL4' THEN AC.EXCH_RATE END) AS EXCH_RATE_4
  ,MAX(CASE WHEN AC.EVENT = 'EVENT_VAL4' THEN AC.LCY_AMOUNT END) AS LCY_AMOUNT_4
  -- 公共字段（所有行值一致，直接聚合提取）
  ,MAX(AC.RELATED_CUSTOMER) AS RELATED_CUSTOMER
FROM ACVW_ALL_AC_ENTRIES AC 
WHERE AC.TRN_REF_NO = '001INFT233140130'
GROUP BY AC.TRN_REF_NO;

注：需要把EVENT_VAL1/EVENT_VAL2等替换为实际数据中的EVENT或TRN_CODE值；如果某些字段在所有行中值相同，直接用聚合函数提取即可。

方法二：使用Oracle PIVOT 语法 #

如果需要合并的字段规则清晰，可以用Oracle原生的PIVOT语法实现行转列：

WITH src_data AS (
    SELECT 
       AC.TRN_REF_NO
      ,AC.EVENT
      ,AC.AC_NO
      ,AC.AC_CCY
      ,AC.FCY_AMOUNT
      ,AC.EXCH_RATE
      ,AC.LCY_AMOUNT
      ,AC.RELATED_CUSTOMER
    FROM ACVW_ALL_AC_ENTRIES AC 
    WHERE AC.TRN_REF_NO = '001INFT233140130'
)
SELECT 
   TRN_REF_NO
  ,RELATED_CUSTOMER
  -- 提取PIVOT后的各列
  ,AC_NO_EVENT1, AC_CCY_EVENT1, FCY_AMOUNT_EVENT1, EXCH_RATE_EVENT1, LCY_AMOUNT_EVENT1
  ,AC_NO_EVENT2, AC_CCY_EVENT2, FCY_AMOUNT_EVENT2, EXCH_RATE_EVENT2, LCY_AMOUNT_EVENT2
  ,AC_NO_EVENT3, AC_CCY_EVENT3, FCY_AMOUNT_EVENT3, EXCH_RATE_EVENT3, LCY_AMOUNT_EVENT3
  ,AC_NO_EVENT4, AC_CCY_EVENT4, FCY_AMOUNT_EVENT4, EXCH_RATE_EVENT4, LCY_AMOUNT_EVENT4
FROM src_data
PIVOT (
    MAX(AC_NO) AS AC_NO, 
    MAX(AC_CCY) AS AC_CCY, 
    MAX(FCY_AMOUNT) AS FCY_AMOUNT,
    MAX(EXCH_RATE) AS EXCH_RATE,
    MAX(LCY_AMOUNT) AS LCY_AMOUNT
    FOR EVENT IN (
        'EVENT_VAL1' AS EVENT1,
        'EVENT_VAL2' AS EVENT2,
        'EVENT_VAL3' AS EVENT3,
        'EVENT_VAL4' AS EVENT4
    )
);

注：PIVOT子句中需要指定要聚合的字段，以及用于区分行的EVENT（或其他标识字段）的具体值，最终会生成对应后缀的列名。

内容的提问来源于stack exchange，提问作者DM19