SQL Server 2019行转列日期对齐问题求助

问题说明 #

在SQL Server 2019中对考勤数据表执行行转列操作时，现有PIVOT脚本输出结果中，同一日期的打卡记录未合并到同一行，不符合预期，需修正脚本。

源表结构及数据 #

当前使用脚本 #

SELECT 
    empno,
    [0] as [punch_code0],
    [1] as [punch_code1],
    [2] as [punch_code2],
    [3] as [punch_code3], 
    [4] as [punch_code4],
    [5] as [punch_code5]
FROM 
    biometric_log
PIVOT 
    (MAX(punch_date) FOR punch_code IN ([0], [1], [2], [3], [4], [5])) AS pvt

当前错误输出 #

empno       punch_code0           punch_code1 punch_code2 punch_code3           punch_code4 punch_code5
0000000001  2024-02-01 07:58:00.000 NULL        NULL        NULL                  NULL        NULL
0000000001  NULL                   NULL        NULL        2024-02-01 17:06:00.000 NULL        NULL
0000000001  2024-02-02 07:53:00.000 NULL        NULL        NULL                  NULL        NULL
0000000001  NULL                   NULL        NULL        2024-02-02 17:05:00.000 NULL        NULL
0000000001  2024-02-03 07:49:00.000 NULL        NULL        NULL                  NULL        NULL
0000000001  NULL                   NULL        2024-02-03 16:04:00.000 NULL        NULL        NULL
0000000001  2024-02-05 07:52:00.000 NULL        NULL        NULL                  NULL        NULL
0000000001  NULL                   NULL        NULL        2024-02-05 17:05:00.000 NULL        NULL
0000000001  2024-02-06 07:53:00.000 NULL        NULL        NULL                  NULL        NULL
0000000001  NULL                   NULL        NULL        2024-02-06 17:05:00.000 NULL        NULL

期望输出 #

empno       punch_code0           punch_code1 punch_code2 punch_code3
0000000001  2024-02-01 07:58:00   NULL        NULL        2024-02-01 17:06:00
0000000001  2024-02-02 07:53:00   NULL        NULL        2024-02-02 17:05:00
0000000001  2024-02-03 07:49:00   NULL        NULL        2024-02-03 16:04:00
0000000001  2024-02-05 07:52:00   NULL        NULL        2024-02-05 17:05:00
0000000001  2024-02-06 07:53:00   NULL        NULL        2024-02-06 17:05:00

修正方案及脚本 #

问题原因 #

原脚本未按日期维度分组，punch_date字段包含时间戳，PIVOT时会将每个带时间的记录视为独立行，导致同一日期的不同打卡记录无法合并。需先提取日期部分作为分组依据，再执行PIVOT。

修正后的脚本 #

WITH DailyPunch AS (
    SELECT 
        empno,
        CAST(punch_date AS DATE) AS punch_day, -- 提取日期部分作为分组维度
        punch_code,
        punch_date
    FROM biometric_log
)
SELECT 
    empno,
    [0] AS punch_code0,
    [1] AS punch_code1,
    [2] AS punch_code2,
    [3] AS punch_code3
FROM DailyPunch
PIVOT (
    MAX(punch_date) 
    FOR punch_code IN ([0], [1], [2], [3])
) AS pvt
ORDER BY punch_day; -- 按日期排序保证输出顺序符合预期

说明 #

1. 用CTEDailyPunch提取punch_date的日期部分作为punch_day，作为分组的关键维度；
2. PIVOT时会自动按empno和punch_day分组，将同一日期下的不同punch_code记录合并到同一行；
3. 最终按punch_day排序，确保输出顺序与期望一致。

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