如何实现基于派生列名的动态表查询？以STUDENTS表为例

你的原查询中，A.which_name||'_name'只是拼接出了列名的字符串文本，Oracle无法将其识别为实际的表列去取值，因此第三列只会显示类似FIRST_name的字符串，而非对应列的实际数据。

以下是两种无需修改代码即可适配新增*_NAME列的可行方案：

方案1：使用动态SQL生成UNPIVOT语句 #

通过查询数据字典动态生成UNPIVOT语句，自动适配所有*_NAME格式的列：

DECLARE
    v_sql VARCHAR2(4000);
BEGIN
    SELECT 'SELECT student_id, which_name, name_value FROM students UNPIVOT (name_value FOR which_name IN (' ||
           LISTAGG('"' || column_name || '" AS ''' || SUBSTR(column_name, 1, INSTR(column_name, '_')-1) || '''', ', ')
           WITHIN GROUP (ORDER BY column_id) || '))'
    INTO v_sql
    FROM all_tab_columns
    WHERE table_name = 'STUDENTS'
      AND column_name LIKE '%_NAME'
      AND owner = USER; -- 指定当前用户，避免跨用户同名表干扰

    EXECUTE IMMEDIATE v_sql;
END;
/

说明 #

• 利用all_tab_columns获取所有符合*_NAME规则的列，通过LISTAGG拼接成UNPIVOT的IN子句，将每个列转换为对应的which_name（如FIRST_NAME转为FIRST）
• 执行动态生成的SQL，直接得到期望的行转列结果

方案2：纯SQL通过XMLTABLE实现动态行转列 #

无需编写PL/SQL块，借助XML解析实现动态识别列：

SELECT s.student_id,
       t.which_name,
       x.name_value
FROM students s,
     XMLTABLE('/ROW/*'
              PASSING XMLTYPE(s)
              COLUMNS 
                  column_name VARCHAR2(100) PATH 'local-name()',
                  name_value VARCHAR2(150) PATH '.'
             ) x,
     (SELECT column_name, SUBSTR(column_name, 1, INSTR(column_name, '_')-1) which_name
      FROM all_tab_columns
      WHERE table_name = 'STUDENTS'
        AND column_name LIKE '%_NAME'
        AND owner = USER) t
WHERE x.column_name = t.column_name
ORDER BY s.student_id, t.column_name;

说明 #

• 将students表的每行数据转换为XML格式，通过XMLTABLE解析每个节点，提取列名和对应值
• 关联数据字典中*_NAME列的信息，映射得到which_name，最终输出目标结果
• 新增*_NAME格式的列后，无需修改SQL即可自动包含

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