Oracle中使用日期字段实现动态Pivot转置的SQL查询问题

问题根因 #

Oracle 原生静态PIVOT语法要求FOR ... IN后的转置列值必须为硬编码常量，直接嵌套动态查询会报语法错误，要实现无日期硬编码的动态转置，可使用以下两种方案：

方案1：PL/SQL动态SQL实现（适用所有Oracle版本，可直接返回结构化结果集） #

你可以先拼接动态生成的日期列列表，再执行组装后的SQL，示例代码如下：

DECLARE
    v_sql VARCHAR2(32767);
    v_date_list VARCHAR2(32767);
BEGIN
    -- 先获取所有动态日期值，拼接为PIVOT需要的格式
    SELECT LISTAGG(''''||TO_CHAR(starttime, 'YYYY/MM/DD')||''' AS "'||TO_CHAR(starttime, 'YYYY/MM/DD')||'"', ',') 
    INTO v_date_list
    FROM (
        SELECT DISTINCT TRUNC(starttime) starttime
        FROM r_test_record
        WHERE sn IN (SELECT sn FROM r_sn WHERE skuno = '750-062572')
          AND messtation = 'SI_V1'
          AND starttime BETWEEN TO_DATE ('2020/06/02 01:00:49','YYYY/MM/DD HH24:MI:SS')
                            AND TO_DATE ('2021/10/02 01:53:49','YYYY/MM/DD HH24:MI:SS')
        ORDER BY starttime
    );

    -- 组装完整的PIVOT查询语句
    v_sql := '
    SELECT * FROM (
        SELECT 
            TO_CHAR(STARTTIME, ''YYYY/MM/DD'') AS DATETIME,
            DECODE(STATE,''P'',''PASS'',''F'',''FAIL'', STATE) AS STATUS
        FROM R_TEST_RECORD
        WHERE SN IN (SELECT SN FROM R_SN WHERE SKUNO = ''750-062572'')
          AND MESSTATION = ''SI_V1''
          AND STARTTIME BETWEEN TO_DATE (''2020/06/02 01:00:49'', ''YYYY/MM/DD HH24:MI:SS'')
                            AND TO_DATE (''2021/10/02 01:53:49'', ''YYYY/MM/DD HH24:MI:SS'')
    )
    PIVOT (
        COUNT(*) AS QTY
        FOR DATETIME IN ('||v_date_list||')
    )';

    -- 执行查询，如需输出结果可使用REF_CURSOR承接，或直接打印语句后手动执行
    DBMS_OUTPUT.PUT_LINE(v_sql);
    -- 若要返回结果集可使用：OPEN :result_cursor FOR v_sql;
END;
/

执行后会输出拼接完成的完整PIVOT语句，直接运行即可得到你需要的转置结果，后续调整时间范围无需修改列值部分，自动适配。

方案2：PIVOT XML实现（适用Oracle 12c及以上版本，单条SQL即可完成） #

如果可以接受先输出XML格式结果再解析，可以使用原生支持动态子查询的PIVOT XML语法：

SELECT * FROM (
    SELECT 
        TO_CHAR(STARTTIME, 'YYYY/MM/DD') AS DATETIME,
        DECODE(STATE,'P','PASS','F','FAIL', STATE) AS STATUS
    FROM R_TEST_RECORD
    WHERE SN IN (SELECT SN FROM R_SN WHERE SKUNO = '750-062572')
      AND MESSTATION = 'SI_V1'
      AND STARTTIME BETWEEN TO_DATE ('2020/06/02 01:00:49', 'YYYY/MM/DD HH24:MI:SS')
                        AND TO_DATE ('2021/10/02 01:53:49', 'YYYY/MM/DD HH24:MI:SS')
)
PIVOT XML (
    COUNT(*) AS QTY
    FOR DATETIME IN (
        SELECT DISTINCT TO_CHAR(starttime, 'YYYY/MM/DD') 
        FROM r_test_record
        WHERE sn IN (SELECT sn FROM r_sn WHERE skuno = '750-062572')
          AND messtation = 'SI_V1'
          AND starttime BETWEEN TO_DATE ('2020/06/02 01:00:49','YYYY/MM/DD HH24:MI:SS')
                            AND TO_DATE ('2021/10/02 01:53:49','YYYY/MM/DD HH24:MI:SS')
    )
)

返回结果中的DATETIME_XML字段存储了所有日期对应的统计值，可根据需要使用XMLQUERY、XMLTABLE函数解析为结构化列。

内容的提问来源于stack exchange，提问作者A Rifkin Guy