Oracle SQL中如何基于现有列筛选并生成目标新列？

原始表结构及数据 #

期望输出表结构及数据 #

解决方案SQL #

固定匹配写法（适合列名明确的场景） #

SELECT
    SUM(CASE WHEN NAME = 'AFF_B_BCORE' THEN GDP END) AS GDP_B_BCORE,
    SUM(CASE WHEN NAME = 'AFF_C_CCORE' THEN GDP END) AS GDP_C_CCORE,
    SUM(CASE WHEN NAME = 'AFF_J_CCORE' THEN GDP END) AS GDP_J_JCORE,
    SUM(CASE WHEN LEVEL = 'B' THEN "EXP BC" END) AS "EXP BC",
    SUM(CASE WHEN LEVEL = 'B' THEN "EXP CC" END) AS "EXP CC",
    SUM(CASE WHEN LEVEL = 'J' THEN "EXP BC" END) AS "EXP JC"
FROM
    your_table_name
WHERE
    NAME NOT LIKE 'AFF_OTHER';

通用匹配写法（适合NAME格式固定的场景） #

如果NAME遵循AFF_X_XCORE的格式，可用正则提取LEVEL部分，适配列名的小差异：

SELECT
    SUM(CASE WHEN REGEXP_SUBSTR(NAME, 'AFF_([A-Z])_([A-Z]+)CORE', 1, 1, 'i', 1) = 'B' THEN GDP END) AS GDP_B_BCORE,
    SUM(CASE WHEN REGEXP_SUBSTR(NAME, 'AFF_([A-Z])_([A-Z]+)CORE', 1, 1, 'i', 1) = 'C' THEN GDP END) AS GDP_C_CCORE,
    SUM(CASE WHEN REGEXP_SUBSTR(NAME, 'AFF_([A-Z])_([A-Z]+)CORE', 1, 1, 'i', 1) = 'J' THEN GDP END) AS GDP_J_JCORE,
    SUM(CASE WHEN LEVEL = 'B' THEN "EXP BC" END) AS "EXP BC",
    SUM(CASE WHEN LEVEL = 'B' THEN "EXP CC" END) AS "EXP CC",
    SUM(CASE WHEN LEVEL = 'J' THEN "EXP BC" END) AS "EXP JC"
FROM
    your_table_name
WHERE
    NAME LIKE 'AFF_%_CORE';

说明 #

• 用SUM(CASE ...)实现行转列：CASE语句匹配指定条件返回对应值，不满足则返回NULL，SUM自动忽略NULL，最终得到目标列的唯一有效值。
• 列名包含空格时（如EXP BC），需用双引号包裹（Oracle对特殊字符/空格列名的语法要求）。
• 通过WHERE子句过滤AFF_OTHER记录，避免无关数据干扰计算。

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