Oracle SQL使用PIVOT实现行转列的问题求助

问题核心错误点 #

• 选择了错误的转列字段：你需要将workdate转为列标题，FOR子句后应该跟workdate而非employee
• SELECT子句错误保留原表字段：PIVOT转换后不会保留聚合源字段、转列字段的原始列，你不需要手动指定原字段，直接查PIVOT后的结果集即可
• 行维度设置错误：你需要employee作为行标题，PIVOT会自动将除聚合字段、转列字段外的所有字段作为行分组维度，无需额外配置

正确实现代码 #

静态PIVOT需要提前列出所有要转为列的日期值，示例如下（可根据你的实际日期范围调整IN子句内容）：

SELECT *
FROM SHIFT
PIVOT (
    -- 同一个员工同一天只有1条排班记录，用MAX/MIN均可满足聚合要求
    MAX(shift)
    FOR workdate IN (
        DATE'2024-05-01' AS "2024-05-01",
        DATE'2024-05-02' AS "2024-05-02",
        DATE'2024-05-03' AS "2024-05-03",
        DATE'2024-05-04' AS "2024-05-04"
    )
)
ORDER BY employee;

如果workdate是字符串类型，直接替换IN子句里的值为字符串格式即可，比如'2024-05-01' AS "2024-05-01"。

动态列扩展说明 #

如果需要自动适配所有存在的日期作为列，Oracle原生静态PIVOT不支持动态IN参数，可以通过拼接动态SQL的方式实现：

DECLARE
    v_sql VARCHAR2(4000);
    v_dates VARCHAR2(2000);
BEGIN
    -- 拼接所有需要转列的日期
    SELECT LISTAGG('DATE'''||to_char(workdate,'yyyy-mm-dd')||''' AS "'||to_char(workdate,'yyyy-mm-dd')||'"', ',') 
    INTO v_dates
    FROM (SELECT DISTINCT workdate FROM SHIFT ORDER BY workdate);
    
    v_sql := 'SELECT * FROM SHIFT PIVOT (MAX(shift) FOR workdate IN ('||v_dates||')) ORDER BY employee';
    -- 执行动态SQL，可根据需要用REF_CURSOR返回结果
    DBMS_OUTPUT.PUT_LINE(v_sql);
    EXECUTE IMMEDIATE v_sql;
END;
/

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