如何将一维排班表转为二维表？SQL查询结果异常求助

问题描述 #

原始数据表结构及数据：

注：Weekday的1-5分别代表周一至周五。

期望转换为如下二维表：

原SQL查询执行后出现重复行、数据分散的问题，结果不符合预期。

错误原因分析 #

原查询使用cross join关联同表导致生成大量重复行，且left join的条件逻辑错误，同时group by未对各时段字段做聚合处理，最终导致数据分散在多行。

修正后的SQL方案 #

方案1：条件聚合（通用型，适用于多数SQL数据库） #

-- 创建临时表并插入数据
Create table #tbl1 (Weektype INT, Weekday varchar(3), Time varchar(20))
Insert into #tbl1 (Weektype,Weekday,Time)
values (1,'1','0800-1400'),(1,'2','0900-1500'),(1,'3','0800-1500'),(1,'4','0800-1530'),(6,'5','0800-1300'),(7,'5','0900-1400')

-- 查询语句
SELECT 
    Weektype,
    MAX(CASE WHEN Weekday = '1' THEN Time END) AS [周一(Mon)],
    MAX(CASE WHEN Weekday = '2' THEN Time END) AS [周二(Tue)],
    MAX(CASE WHEN Weekday = '3' THEN Time END) AS [周三(Wed)],
    MAX(CASE WHEN Weekday = '4' THEN Time END) AS [周四(Thu)],
    MAX(CASE WHEN Weekday = '5' THEN Time END) AS [周五(Fri)]
FROM #tbl1
GROUP BY Weektype
ORDER BY Weektype

-- 清理临时表
Drop table #tbl1

方案2：使用PIVOT（SQL Server专用） #

-- 创建临时表并插入数据
Create table #tbl1 (Weektype INT, Weekday varchar(3), Time varchar(20))
Insert into #tbl1 (Weektype,Weekday,Time)
values (1,'1','0800-1400'),(1,'2','0900-1500'),(1,'3','0800-1500'),(1,'4','0800-1530'),(6,'5','0800-1300'),(7,'5','0900-1400')

-- 查询语句
SELECT 
    Weektype,
    [1] AS [周一(Mon)],
    [2] AS [周二(Tue)],
    [3] AS [周三(Wed)],
    [4] AS [周四(Thu)],
    [5] AS [周五(Fri)]
FROM #tbl1
PIVOT (
    MAX(Time)
    FOR Weekday IN ([1],[2],[3],[4],[5])
) AS PivotTable
ORDER BY Weektype

-- 清理临时表
Drop table #tbl1

说明 #

两种方案均通过按Weektype分组，将不同Weekday对应的Time值聚合到同一行的对应列中，完美匹配预期输出格式。条件聚合兼容性更强，PIVOT语法更简洁但仅适用于支持该特性的数据库（如SQL Server）。

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