使用SQL Pivot将行转列生成员工考勤报表

需要将包含PDate（打卡日期）、EmpCode（员工编号）、EmpName（员工姓名）、PStatus（出勤状态）、OT（加班时长）的打卡数据表，转置为以日期为列、每行分别展示出勤状态和加班时长的考勤报表。

原始打卡数据表（AttendanceRecords） #

目标输出报表 #

解决方案1：静态日期范围（固定日期区间适用） #

如果报表的日期范围固定，直接用UNION ALL拆分状态和加班记录，结合条件聚合实现转置：

-- 出勤状态行
SELECT
    EmpCode AS EmpID,
    EmpName,
    MAX(CASE WHEN PDate = '01-01-2023' THEN PStatus ELSE NULL END) AS '01-01-2023',
    MAX(CASE WHEN PDate = '02-01-2023' THEN PStatus ELSE NULL END) AS '02-01-2023',
    MAX(CASE WHEN PDate = '03-01-2023' THEN PStatus ELSE NULL END) AS '03-01-2023',
    MAX(CASE WHEN PDate = '04-01-2023' THEN PStatus ELSE NULL END) AS '04-01-2023',
    MAX(CASE WHEN PDate = '05-01-2023' THEN PStatus ELSE NULL END) AS '05-01-2023',
    MAX(CASE WHEN PDate = '06-01-2023' THEN PStatus ELSE NULL END) AS '06-01-2023',
    MAX(CASE WHEN PDate = '07-01-2023' THEN PStatus ELSE NULL END) AS '07-01-2023',
    MAX(CASE WHEN PDate = '08-01-2023' THEN PStatus ELSE NULL END) AS '08-01-2023',
    MAX(CASE WHEN PDate = '09-01-2023' THEN PStatus ELSE NULL END) AS '09-01-2023',
    MAX(CASE WHEN PDate = '10-01-2023' THEN PStatus ELSE NULL END) AS '10-01-2023',
    MAX(CASE WHEN PDate = '11-01-2023' THEN PStatus ELSE NULL END) AS '11-01-2023',
    MAX(CASE WHEN PDate = '12-01-2023' THEN PStatus ELSE NULL END) AS '12-01-2023',
    MAX(CASE WHEN PDate = '13-01-2023' THEN PStatus ELSE NULL END) AS '13-01-2023',
    MAX(CASE WHEN PDate = '14-01-2023' THEN PStatus ELSE NULL END) AS '14-01-2023',
    MAX(CASE WHEN PDate = '15-01-2023' THEN PStatus ELSE NULL END) AS '15-01-2023'
FROM AttendanceRecords
WHERE EmpCode = '19440' -- 可调整为查询全体员工
GROUP BY EmpCode, EmpName

UNION ALL

-- 加班时长行
SELECT
    EmpCode AS EmpID,
    EmpName,
    COALESCE(MAX(CASE WHEN PDate = '01-01-2023' THEN OT ELSE NULL END), 0) AS '01-01-2023',
    COALESCE(MAX(CASE WHEN PDate = '02-01-2023' THEN OT ELSE NULL END), 0) AS '02-01-2023',
    COALESCE(MAX(CASE WHEN PDate = '03-01-2023' THEN OT ELSE NULL END), 0) AS '03-01-2023',
    COALESCE(MAX(CASE WHEN PDate = '04-01-2023' THEN OT ELSE NULL END), 0) AS '04-01-2023',
    COALESCE(MAX(CASE WHEN PDate = '05-01-2023' THEN OT ELSE NULL END), 0) AS '05-01-2023',
    COALESCE(MAX(CASE WHEN PDate = '06-01-2023' THEN OT ELSE NULL END), 0) AS '06-01-2023',
    COALESCE(MAX(CASE WHEN PDate = '07-01-2023' THEN OT ELSE NULL END), 0) AS '07-01-2023',
    COALESCE(MAX(CASE WHEN PDate = '08-01-2023' THEN OT ELSE NULL END), 0) AS '08-01-2023',
    COALESCE(MAX(CASE WHEN PDate = '09-01-2023' THEN OT ELSE NULL END), 0) AS '09-01-2023',
    COALESCE(MAX(CASE WHEN PDate = '10-01-2023' THEN OT ELSE NULL END), 0) AS '10-01-2023',
    COALESCE(MAX(CASE WHEN PDate = '11-01-2023' THEN OT ELSE NULL END), 0) AS '11-01-2023',
    COALESCE(MAX(CASE WHEN PDate = '12-01-2023' THEN OT ELSE NULL END), 0) AS '12-01-2023',
    COALESCE(MAX(CASE WHEN PDate = '13-01-2023' THEN OT ELSE NULL END), 0) AS '13-01-2023',
    COALESCE(MAX(CASE WHEN PDate = '14-01-2023' THEN OT ELSE NULL END), 0) AS '14-01-2023',
    COALESCE(MAX(CASE WHEN PDate = '15-01-2023' THEN OT ELSE NULL END), 0) AS '15-01-2023'
FROM AttendanceRecords
WHERE EmpCode = '19440' -- 可调整为查询全体员工
GROUP BY EmpCode, EmpName;

解决方案2：动态日期范围（日期不固定适用） #

如果报表日期范围动态变化，用动态SQL自动生成列（以MySQL为例）：

-- 生成日期列的SQL片段
SET @cols = NULL;
SELECT GROUP_CONCAT(DISTINCT
    CONCAT('MAX(CASE WHEN PDate = ''', PDate, ''' THEN PStatus ELSE NULL END) AS ''', PDate, '''')
) INTO @cols
FROM AttendanceRecords;

SET @cols_ot = NULL;
SELECT GROUP_CONCAT(DISTINCT
    CONCAT('COALESCE(MAX(CASE WHEN PDate = ''', PDate, ''' THEN OT ELSE NULL END), 0) AS ''', PDate, '''')
) INTO @cols_ot
FROM AttendanceRecords;

-- 拼接完整SQL并执行
SET @sql = CONCAT('
SELECT EmpCode AS EmpID, EmpName, ', @cols, '
FROM AttendanceRecords
GROUP BY EmpCode, EmpName
UNION ALL
SELECT EmpCode AS EmpID, EmpName, ', @cols_ot, '
FROM AttendanceRecords
GROUP BY EmpCode, EmpName
');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

补充说明 #

• 要是原始数据里同一员工同一天有多条打卡记录，想生成AB/P这种合并状态，把MAX(PStatus)换成GROUP_CONCAT(DISTINCT PStatus SEPARATOR '/')就行。
• COALESCE函数用来把空的加班时长转成0，和目标格式匹配。
• 要是用SQL Server、Oracle这类数据库，动态SQL写法会有差异，但核心逻辑一致：先拆分状态和加班记录，再用条件聚合或PIVOT实现转置。

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