Oracle SQL 动态将competency表行转列生成员工视图问题咨询

标准SQL的PIVOT语法要求IN子句内必须是静态常量值，不支持动态子查询传入列名，这是SQL语法的硬性限制，所以你之前的写法会报错。

通用解决方案：动态SQL #

几乎所有主流数据库（Oracle、MySQL、SQL Server、PostgreSQL等）都支持动态拼接SQL的方式实现动态行转列，步骤如下：

1. 先查询competency_tab表中所有唯一的competency_id，拼接成'值' AS 别名格式的列字符串
2. 将拼接好的列字符串代入PIVOT的IN子句，生成完整的查询语句
3. 执行动态生成的SQL，也可以直接用该语句创建视图

以下是Oracle环境的示例代码，已经关联员工表逻辑（假设员工能力映射表为emp_competency，存储员工ID、能力ID、对应能力值，员工表为employee）：

DECLARE
    -- 存储拼接后的动态列字符串
    v_pivot_columns VARCHAR2(32767);
    -- 存储完整的动态SQL语句
    v_full_sql VARCHAR2(32767);
BEGIN
    -- 拼接所有动态列
    SELECT LISTAGG('''' || competency_id || ''' AS "' || competency_id || '"', ',')
    WITHIN GROUP (ORDER BY competency_id)
    INTO v_pivot_columns
    FROM (SELECT DISTINCT competency_id FROM competency_tab);

    -- 拼接创建视图的完整SQL
    v_full_sql := '
    CREATE OR REPLACE VIEW employee_competency_view AS
    SELECT emp_id, emp_name, ' || v_pivot_columns || '
    FROM (
        SELECT 
            e.emp_id, 
            e.emp_name,
            ec.competency_id,
            ec.competency_value
        FROM employee e
        LEFT JOIN emp_competency ec ON e.emp_id = ec.emp_id
    )
    PIVOT (
        -- 聚合函数根据实际业务调整，存的是能力分/等级用MAX，计数用COUNT
        MAX(competency_value)
        FOR competency_id IN (' || v_pivot_columns || ')
    )';

    -- 执行动态SQL生成视图
    EXECUTE IMMEDIATE v_full_sql;
END;
/

自动更新视图的补充方案 #

因为你提到competency表支持用户随时增改，而视图定义是静态的，不会自动同步新增的能力项，你可以给competency_tab加增删改触发器，当表数据发生变更时自动执行上面的动态SQL刷新视图结构即可。

其他可选方案 #

如果不想在数据库层维护动态SQL，也可以直接在上层应用/BI工具侧做行转列逻辑，适配能力项的变化会更灵活，无需频繁修改数据库视图。

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