如何调整SQL在Oracle Apex 23.2中生成按日期列的考勤报表

静态列转换（固定日期范围） #

如果报表需要展示的日期是明确固定的，用Oracle原生PIVOT子句直接实现行列转换：

SELECT contact_name,
       "2024-05-01", "2024-05-02", "2024-05-03"
FROM (
    SELECT 
        vc.contact_name,
        TO_CHAR(ve.event_date, 'YYYY-MM-DD') AS event_date_str,
        va.attendance_type
    FROM vol_contact vc
    LEFT JOIN vol_attendance va 
        ON vc.contact_id = va.contact_id
    LEFT JOIN vol_event ve 
        ON va.event_id = ve.event_id
    WHERE ve.event_date BETWEEN DATE '2024-05-01' AND DATE '2024-05-03'
) src
PIVOT (
    MAX(attendance_type)
    FOR event_date_str IN (
        '2024-05-01' AS "2024-05-01",
        '2024-05-02' AS "2024-05-02",
        '2024-05-03' AS "2024-05-03"
    )
) pvt
ORDER BY contact_name;

关键说明 #

• 子查询src先关联三张表，提取核心数据：联系人姓名、格式化后的活动日期、考勤类型（'I'/'Z'）
• PIVOT子句将日期字符串转为表头列，用MAX(attendance_type)确保每个联系人在单日期下仅展示一条有效考勤记录（空值代表无考勤）
• 用LEFT JOIN保留所有联系人，即使没有任何考勤记录

动态列转换（日期范围不固定） #

如果报表需要展示的日期是动态变化的（比如最近30天、本月所有活动日），需要用动态SQL生成列名：

DECLARE
    v_col_list VARCHAR2(4000);
    v_full_sql VARCHAR2(4000);
BEGIN
    -- 生成动态列名：提取目标日期范围内的所有唯一活动日期，格式化为PIVOT需要的字符串
    SELECT LISTAGG(
        '''' || TO_CHAR(event_date, 'YYYY-MM-DD') || ''' AS "' || TO_CHAR(event_date, 'YYYY-MM-DD') || '"', 
        ', '
    ) WITHIN GROUP (ORDER BY event_date)
    INTO v_col_list
    FROM (
        SELECT DISTINCT event_date
        FROM vol_event
        WHERE event_date >= TRUNC(SYSDATE) - 30 -- 这里定义动态日期范围，比如最近30天
    );

    -- 拼接完整的PIVOT查询语句
    v_full_sql := '
        SELECT contact_name, ' || v_col_list || '
        FROM (
            SELECT 
                vc.contact_name,
                TO_CHAR(ve.event_date, ''YYYY-MM-DD'') AS event_date_str,
                va.attendance_type
            FROM vol_contact vc
            LEFT JOIN vol_attendance va 
                ON vc.contact_id = va.contact_id
            LEFT JOIN vol_event ve 
                ON va.event_id = ve.event_id
            WHERE ve.event_date >= TRUNC(SYSDATE) - 30
        ) src
        PIVOT (
            MAX(attendance_type)
            FOR event_date_str IN (' || v_col_list || ')
        ) pvt
        ORDER BY contact_name';

    -- 执行动态SQL（在Apex中可绑定到报表区域，或用DBMS_OUTPUT测试）
    EXECUTE IMMEDIATE v_full_sql;
END;
/

关键说明 #

• 用LISTAGG函数将所有目标日期拼接成PIVOT需要的列定义字符串
• 动态SQL会自动适配日期范围变化，无需手动修改列名
• 在Apex中可将这段逻辑封装为存储过程，或直接在报表区域使用动态SQL数据源

Apex可视化配置方案（无需手写复杂SQL） #

如果使用Apex交互式报表，可直接通过可视化操作生成交叉报表：

1. 创建基础报表：关联VOL_CONTACT、VOL_ATTENDANCE、VOL_EVENT三张表，展示contact_name、event_date、attendance_type三个字段
2. 进入报表的「Actions」→「Pivot」菜单
3. 拖拽字段到对应区域：
   • 将contact_name拖到「Rows」区域
   • 将event_date拖到「Columns」区域
   • 将attendance_type拖到「Values」区域，聚合函数选择「Max」
4. 自动生成联系人作为行、日期作为列的交叉报表，单元格自动显示'I'/'Z'或空值

注意事项 #

• 确保VOL_ATTENDANCE表的attendance_type字段存储值为'I'（In-Person）或'Z'（Zoom），如果存储的是其他值，需在查询中用CASE转换：

  CASE va.attendance_type
      WHEN 'IN_PERSON' THEN 'I'
      WHEN 'ZOOM' THEN 'Z'
      ELSE NULL
  END AS attendance_type
  

• 如果需要强制显示所有联系人（包括无任何考勤记录的），必须使用LEFT JOIN关联三张表

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