Oracle SQL如何将STAT_DATE设为首列实现多维度透视统计？

问题背景 #

现有Oracle表DEPARTMENT_STATISTICS，结构如下：

SELECT IDNO,
       DEPT_ID,
       SAMPLES_MORNING AS SM,
       TESTS_MORNING   AS TM,
       SAMPLES_EVENING AS SE,
       TESTS_EVENING   AS TE,
       SENT_OUT        AS SO,
       STAT_DATE,
       HOSPITAL_NO,
       USER_ID
FROM DEPARTMENT_STATISTICS

需要生成每日各医院的样本与检测量汇总报表，期望输出格式（多层表头）：

原尝试SQL：

SELECT * FROM
(
  SELECT dept_id , hospital_no,STAT_DATE, SAMPLES_MORNING , TESTS_MORNING , samples_evening , TESTS_EVENING , SENT_OUT FROM DEPARTMENT_STATISTICS
)
PIVOT
(
  sum(SAMPLES_MORNING) as SM , sum(TESTS_MORNING) as TM, sum(samples_evening) AS SE , sum(TESTS_EVENING) as TE , SUM(SENT_OUT) AS OUT
  FOR hospital_no in (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,21,22,41,61,81,107,108,109,110)
);

遇到的问题：STAT_DATE显示在行数据中，无法实现STAT_DATE作为表头分组、HOSPITAL_NO置于表头分层的效果。

解决方案 #

1. 理解SQL结果集限制 #

纯Oracle SQL无法直接生成多层表头的结果，因为SQL返回的是二维表结构，列名是单层的。但可以生成包含日期、医院、指标组合的列，再通过报表工具（如Oracle Reports、BI Publisher）或前端工具设置多层表头。

2. 调整PIVOT逻辑，将STAT_DATE纳入转置维度 #

要让STAT_DATE作为表头分组，需将其与HOSPITAL_NO一起作为PIVOT的转置维度，同时确保DEPT_ID作为行维度保留：

SELECT DEPT_ID,
       -- 按日期+医院组合的列，按需扩展其他日期和医院
       "20250601_H2_SM" AS "01/06/2025_2_SM",
       "20250601_H2_TM" AS "01/06/2025_2_TM",
       "20250601_H3_SM" AS "01/06/2025_3_SM",
       "20250601_H3_TM" AS "01/06/2025_3_TM",
       "20250602_H2_SM" AS "02/06/2025_2_SM",
       "20250602_H2_TM" AS "02/06/2025_2_TM",
       "20250602_H3_SM" AS "02/06/2025_3_SM",
       "20250602_H3_TM" AS "02/06/2025_3_TM"
       -- 可继续添加SE、TE、SO及其他医院/日期的组合列
FROM (
    SELECT 
        DEPT_ID,
        STAT_DATE,
        HOSPITAL_NO,
        SAMPLES_MORNING AS SM,
        TESTS_MORNING AS TM,
        SAMPLES_EVENING AS SE,
        TESTS_EVENING AS TE,
        SENT_OUT AS SO
    FROM DEPARTMENT_STATISTICS
)
PIVOT (
    SUM(SM) AS SM, SUM(TM) AS TM, SUM(SE) AS SE, SUM(TE) AS TE, SUM(SO) AS SO
    FOR (STAT_DATE, HOSPITAL_NO) IN (
        (DATE '2025-06-01', 2) AS "20250601_H2",
        (DATE '2025-06-01', 3) AS "20250601_H3",
        (DATE '2025-06-02', 2) AS "20250602_H2",
        (DATE '2025-06-02', 3) AS "20250602_H3"
        -- 替换为实际需要统计的日期和医院编号，可扩展更多组合
    )
)
ORDER BY DEPT_ID;

3. 实现多层表头的补充说明 #

生成上述结果后，在报表工具中：

• 将DEPT_ID设为第一列
• 对组合列名（如01/06/2025_2_SM）进行分组设置：
  • 第一层表头：按日期分组（如01/06/2025）
  • 第二层表头：按医院编号分组（如2）
  • 第三层表头：按指标分组（如SM）

4. 动态日期处理（可选） #

如果需要动态适配所有存在的STAT_DATE，需使用动态SQL生成PIVOT语句，示例如下：

DECLARE
    v_pivot_cols VARCHAR2(4000);
BEGIN
    -- 生成所有日期+医院的组合列
    SELECT LISTAGG('(DATE ''' || TO_CHAR(STAT_DATE, 'YYYY-MM-DD') || ''', ' || HOSPITAL_NO || ') AS "' || TO_CHAR(STAT_DATE, 'YYYYMMDD') || '_H' || HOSPITAL_NO || '"', ', ')
    INTO v_pivot_cols
    FROM (SELECT DISTINCT STAT_DATE, HOSPITAL_NO FROM DEPARTMENT_STATISTICS);
    
    -- 执行动态PIVOT查询
    EXECUTE IMMEDIATE '
        SELECT DEPT_ID, ' || 
        LISTAGG('"' || TO_CHAR(STAT_DATE, 'YYYYMMDD') || '_H' || HOSPITAL_NO || '_SM" AS "' || TO_CHAR(STAT_DATE, 'DD/MM/YYYY') || '_' || HOSPITAL_NO || '_SM", ' ||
                '"' || TO_CHAR(STAT_DATE, 'YYYYMMDD') || '_H' || HOSPITAL_NO || '_TM" AS "' || TO_CHAR(STAT_DATE, 'DD/MM/YYYY') || '_' || HOSPITAL_NO || '_TM"', ', ')
        OVER () || '
        FROM (
            SELECT DEPT_ID, STAT_DATE, HOSPITAL_NO, SAMPLES_MORNING AS SM, TESTS_MORNING AS TM
            FROM DEPARTMENT_STATISTICS
        )
        PIVOT (
            SUM(SM) AS SM, SUM(TM) AS TM
            FOR (STAT_DATE, HOSPITAL_NO) IN (' || v_pivot_cols || ')
        )
        ORDER BY DEPT_ID';
END;
/

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