SQL使用Pivot行转列统计司机每日各工作状态时长实现问题

针对你的需求，这里提供两种实现方式，优先推荐条件聚合方案，逻辑更清晰易维护。

方案1：条件聚合实现（无需PIVOT，更推荐） #

固定枚举值的行转列场景下，条件聚合语法更直观，兼容性更强，不需要额外处理PIVOT的分组规则：

CREATE VIEW vwWorkingTimesPerDay AS
SELECT  
    DriverName AS Name,
    MIN(StartDateAndTime) AS StartDateAndTime,
    MAX(EndDateAndTime) AS EndDateAndTime,
    MIN(StartPositionText) AS StartText,
    MIN(EndPositionText) AS EndText,
    -- 格式化Loading状态时长
    RIGHT('00' + CONVERT(VARCHAR, DATEPART(HOUR, DATEADD(SECOND, SUM(CASE WHEN WorkState = 'Loading' THEN DATEDIFF(SECOND, StartDateAndTime, EndDateAndTime) ELSE 0 END), 0))), 2) + ':' +
    RIGHT('00' + CONVERT(VARCHAR, DATEPART(MINUTE, DATEADD(SECOND, SUM(CASE WHEN WorkState = 'Loading' THEN DATEDIFF(SECOND, StartDateAndTime, EndDateAndTime) ELSE 0 END), 0))), 2) + ':' +
    RIGHT('00' + CONVERT(VARCHAR, DATEPART(SECOND, DATEADD(SECOND, SUM(CASE WHEN WorkState = 'Loading' THEN DATEDIFF(SECOND, StartDateAndTime, EndDateAndTime) ELSE 0 END), 0))), 2) AS Loading,
    -- 格式化Driving状态时长
    RIGHT('00' + CONVERT(VARCHAR, DATEPART(HOUR, DATEADD(SECOND, SUM(CASE WHEN WorkState = 'Driving' THEN DATEDIFF(SECOND, StartDateAndTime, EndDateAndTime) ELSE 0 END), 0))), 2) + ':' +
    RIGHT('00' + CONVERT(VARCHAR, DATEPART(MINUTE, DATEADD(SECOND, SUM(CASE WHEN WorkState = 'Driving' THEN DATEDIFF(SECOND, StartDateAndTime, EndDateAndTime) ELSE 0 END), 0))), 2) + ':' +
    RIGHT('00' + CONVERT(VARCHAR, DATEPART(SECOND, DATEADD(SECOND, SUM(CASE WHEN WorkState = 'Driving' THEN DATEDIFF(SECOND, StartDateAndTime, EndDateAndTime) ELSE 0 END), 0))), 2) AS Driving,
    -- 格式化Pause状态时长
    RIGHT('00' + CONVERT(VARCHAR, DATEPART(HOUR, DATEADD(SECOND, SUM(CASE WHEN WorkState = 'Pause' THEN DATEDIFF(SECOND, StartDateAndTime, EndDateAndTime) ELSE 0 END), 0))), 2) + ':' +
    RIGHT('00' + CONVERT(VARCHAR, DATEPART(MINUTE, DATEADD(SECOND, SUM(CASE WHEN WorkState = 'Pause' THEN DATEDIFF(SECOND, StartDateAndTime, EndDateAndTime) ELSE 0 END), 0))), 2) + ':' +
    RIGHT('00' + CONVERT(VARCHAR, DATEPART(SECOND, DATEADD(SECOND, SUM(CASE WHEN WorkState = 'Pause' THEN DATEDIFF(SECOND, StartDateAndTime, EndDateAndTime) ELSE 0 END), 0))), 2) AS Pause,
    CONVERT(DATE, StartDateAndTime) AS DayPerformed
FROM (
    SELECT 
        *,
        StartPositionText = FIRST_VALUE(StartText) OVER (
            PARTITION BY DriverName, CONVERT(DATE, StartDateAndTime)
            ORDER BY StartDateAndTime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ),
        EndPositionText = LAST_VALUE(EndText) OVER (
            PARTITION BY DriverName, CONVERT(DATE, StartDateAndTime)
            ORDER BY StartDateAndTime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        )
    FROM webfleet.tblWorkingTimes
) t
GROUP BY DriverName, CONVERT(DATE, StartDateAndTime), StartPositionText, EndPositionText

方案2：PIVOT语法实现 #

如果你需要严格使用PIVOT实现行转列，可参考以下代码：

CREATE VIEW vwWorkingTimesPerDay AS
WITH base_data AS (
    SELECT 
        DriverName AS Name,
        StartDateAndTime,
        EndDateAndTime,
        StartText,
        EndText,
        WorkState,
        CONVERT(DATE, StartDateAndTime) AS DayPerformed,
        DATEDIFF(SECOND, StartDateAndTime, EndDateAndTime) AS duration_sec,
        FIRST_VALUE(StartText) OVER (
            PARTITION BY DriverName, CONVERT(DATE, StartDateAndTime)
            ORDER BY StartDateAndTime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS day_first_start,
        LAST_VALUE(EndText) OVER (
            PARTITION BY DriverName, CONVERT(DATE, StartDateAndTime)
            ORDER BY StartDateAndTime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS day_last_end,
        MIN(StartDateAndTime) OVER (PARTITION BY DriverName, CONVERT(DATE, StartDateAndTime)) AS day_start,
        MAX(EndDateAndTime) OVER (PARTITION BY DriverName, CONVERT(DATE, StartDateAndTime)) AS day_end
    FROM webfleet.tblWorkingTimes
),
pivot_data AS (
    SELECT *
    FROM (
        SELECT Name, DayPerformed, day_start, day_end, day_first_start, day_last_end, WorkState, duration_sec
        FROM base_data
    ) t
    PIVOT (
        SUM(duration_sec)
        FOR WorkState IN ([Loading], [Driving], [Pause])
    ) p
)
SELECT 
    Name,
    day_start AS StartDateAndTime,
    day_end AS EndDateAndTime,
    day_first_start AS StartText,
    day_last_end AS EndText,
    -- 格式化各状态时长，逻辑和方案1一致
    RIGHT('00' + CONVERT(VARCHAR, DATEPART(HOUR, DATEADD(SECOND, ISNULL(Loading,0), 0))), 2) + ':' +
    RIGHT('00' + CONVERT(VARCHAR, DATEPART(MINUTE, DATEADD(SECOND, ISNULL(Loading,0), 0))), 2) + ':' +
    RIGHT('00' + CONVERT(VARCHAR, DATEPART(SECOND, DATEADD(SECOND, ISNULL(Loading,0), 0))), 2) AS Loading,
    RIGHT('00' + CONVERT(VARCHAR, DATEPART(HOUR, DATEADD(SECOND, ISNULL(Driving,0), 0))), 2) + ':' +
    RIGHT('00' + CONVERT(VARCHAR, DATEPART(MINUTE, DATEADD(SECOND, ISNULL(Driving,0), 0))), 2) + ':' +
    RIGHT('00' + CONVERT(VARCHAR, DATEPART(SECOND, DATEADD(SECOND, ISNULL(Driving,0), 0))), 2) AS Driving,
    RIGHT('00' + CONVERT(VARCHAR, DATEPART(HOUR, DATEADD(SECOND, ISNULL(Pause,0), 0))), 2) + ':' +
    RIGHT('00' + CONVERT(VARCHAR, DATEPART(MINUTE, DATEADD(SECOND, ISNULL(Pause,0), 0))), 2) + ':' +
    RIGHT('00' + CONVERT(VARCHAR, DATEPART(SECOND, DATEADD(SECOND, ISNULL(Pause,0), 0))), 2) AS Pause,
    DayPerformed
FROM pivot_data
GROUP BY Name, DayPerformed, day_start, day_end, day_first_start, day_last_end, Loading, Driving, Pause

时长格式化优化建议 #

如果你的数据库是SQL Server 2012及以上版本，且确定单日司机总工作时长不会超过24小时，可使用FORMAT函数简化时长格式化逻辑，示例：

FORMAT(DATEADD(SECOND, 总秒数, 0), 'HH:mm:ss')

若存在单日工作超24小时的场景，保留你原有的字符串拼接写法更稳妥，不会出现小时数取模错误。

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