如何正确使用SQL PIVOT实现司机任务时段透视表？

现有表结构与数据 #

CREATE TABLE DriversTask (
Id int IDENTITY(1,1) PRIMARY KEY,
FromTime time(7) NOT NULL,
ToTime time(7) NOT NULL,
AtDate date,
Driver varchar(200),
);


INSERT INTO DriversTask ([FromTime],[ToTime],[AtDate],[Driver])
VALUES
('02:30:00','03:40:00', '2022-08-23', 1),
('04:50:00','05:10:00', '2022-08-23', 1),
('05:40:00','06:00:00', '2022-08-23', 1),
('06:30:00','07:15:00', '2022-08-23', 1),
('07:40:00','08:00:00', '2022-08-23', 1),
('09:48:00','10:40:00', '2022-08-23', 1),
('11:12:00','12:40:00', '2022-08-23', 1),
('13:00:00','14:00:00', '2022-08-23', 1),
('15:30:00','17:40:00', '2022-08-23', 1),
('19:50:00','21:22:00', '2022-08-23', 1),
('22:30:00','00:00:00', '2022-08-23', 1);

需求 #

将司机任务按1-24小时列展开，展示每个时段的任务占用情况，示例格式如下：

Driver 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

1     [  --- ---- ---- --- ----  ---- ---- ----- ----- ---- ---- ----] ...etc

你的错误尝试 #

SELECT [Driver], [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24] FROM   
(SELECT [Driver], TimeFrom, TimeTo FROM DriversTask ) Tab1  
PIVOT  
(  
SUM(TimeFrom) FOR Driver 
IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24])
) AS Tab2

错误原因 #

1. PIVOT转置方向错误：你将Driver作为列转换字段，但实际需要把小时数作为列，Driver作为行维度。
2. SUM(TimeFrom)无业务意义：需求是标记时段是否有任务，而非对时间求和。
3. 未处理跨天任务（如22:30到00:00的任务），也未生成1-24小时的完整维度。

正确实现代码 #

WITH HoursCTE AS (
    -- 生成1到24的小时维度
    SELECT 1 AS HourNum
    UNION ALL
    SELECT HourNum + 1 FROM HoursCTE WHERE HourNum < 24
),
TaskHours AS (
    -- 匹配每个任务覆盖的所有小时
    SELECT 
        dt.Driver,
        h.HourNum
    FROM DriversTask dt
    CROSS JOIN HoursCTE h
    WHERE 
        -- 处理正常非跨天任务：小时在任务开始到结束的小时区间内
        (dt.ToTime > dt.FromTime AND h.HourNum BETWEEN DATEPART(HOUR, dt.FromTime) AND DATEPART(HOUR, dt.ToTime))
        -- 处理跨天任务（结束时间为00:00）：覆盖从开始小时到24点
        OR (dt.ToTime = '00:00:00' AND h.HourNum >= DATEPART(HOUR, dt.FromTime))
)
-- 转置为小时列展示
SELECT 
    Driver,
    ISNULL([1], '') AS [1],
    ISNULL([2], '---') AS [2],
    ISNULL([3], '---') AS [3],
    ISNULL([4], '') AS [4],
    ISNULL([5], '---') AS [5],
    ISNULL([6], '---') AS [6],
    ISNULL([7], '---') AS [7],
    ISNULL([8], '---') AS [8],
    ISNULL([9], '') AS [9],
    ISNULL([10], '---') AS [10],
    ISNULL([11], '') AS [11],
    ISNULL([12], '---') AS [12],
    ISNULL([13], '---') AS [13],
    ISNULL([14], '---') AS [14],
    ISNULL([15], '') AS [15],
    ISNULL([16], '---') AS [16],
    ISNULL([17], '---') AS [17],
    ISNULL([18], '') AS [18],
    ISNULL([19], '') AS [19],
    ISNULL([20], '---') AS [20],
    ISNULL([21], '---') AS [21],
    ISNULL([22], '---') AS [22],
    ISNULL([23], '---') AS [23],
    ISNULL([24], '---') AS [24]
FROM (
    SELECT 
        Driver,
        HourNum,
        '---' AS TaskMark
    FROM TaskHours
) AS Source
PIVOT (
    MAX(TaskMark) FOR HourNum IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24])
) AS PivotTable;

代码说明 #

1. HoursCTE：生成1到24的完整小时维度，确保每个小时都有对应的列。
2. TaskHours：通过交叉连接匹配每个任务覆盖的小时，同时处理跨天任务的特殊情况。
3. PIVOT转置：将小时数作为列，用MAX(TaskMark)标记该时段是否有任务，ISNULL用于填充无任务时段的空值。

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