如何通过SQL将预约数据转换为每周日历格式

数据源说明 #

从vw_turmas_grade视图查询得到基础数据，查询语句：

SELECT 
   CONVERT(VARCHAR(5),T.HORAINI_AULA,108) as HORAINI, reservation, day_week
FROM 
   vw_turmas_grade T

结果集字段含义：

• HORAINI：预约时间（仅需时间部分，日期可忽略）
• reservation：预约项内容
• day_week：周几标识（2=周一，3=周二，4=周三，5=周四，6=周五，7=周六）

目标格式 #

需要将数据转换为时间为行、周几为列的日历格式，示例如下：

现有尝试及问题 #

尝试使用CASE语句实现，但结果不符合预期，查询语句：

WITH Reservation AS 
(SELECT 
   CONVERT(VARCHAR(5),T.HORAINI_AULA,108) as HORAINI,
   reservation, 
   day_week
FROM 
   vw_turmas_grade T)

SELECT 
    HORAINI,
    CASE WHEN R.day_week = '2' THEN R.reservation END as 'Monday',
    CASE WHEN R.day_week = '3' THEN R.reservation END as 'Tuesday',
    CASE WHEN R.day_week = '4' THEN R.reservation END as 'Wednesday',
    CASE WHEN R.day_week = '5' THEN R.reservation END as 'Thursday',
    CASE WHEN R.day_week = '6' THEN R.reservation END as 'Friday',
    CASE WHEN R.day_week = '7' THEN R.reservation END as 'Saturday'
FROM Reservation R

存在的问题：

1. 每个预约项单独成行，其余列显示NULL，无法合并为单一行的时间记录
2. 没有预约的时间点完全不显示

正确解决方案 #

方案1：基于固定时间列表的转换 #

如果需要显示的时间点是固定的（如示例中的时间），可以先生成完整的时间列表，再通过左连接+聚合函数实现转置：

-- 生成所有需要展示的固定时间点
WITH AllTimeSlots AS (
    SELECT '08:15' AS HORAINI UNION ALL
    SELECT '09:45' UNION ALL
    SELECT '11:00' UNION ALL
    SELECT '13:15' UNION ALL
    SELECT '14:15' UNION ALL
    SELECT '14:45' UNION ALL
    SELECT '15:45' UNION ALL
    SELECT '19:15' UNION ALL
    SELECT '20:45' UNION ALL
    SELECT '22:00'
),
-- 整理原始预约数据
ReservationData AS (
    SELECT 
        CONVERT(VARCHAR(5), T.HORAINI_AULA, 108) AS HORAINI,
        reservation,
        day_week
    FROM vw_turmas_grade T
)
SELECT 
    ats.HORAINI,
    -- 用MAX聚合将同一时间的预约项合并到对应周几列
    MAX(CASE WHEN rd.day_week = '2' THEN rd.reservation END) AS Monday,
    MAX(CASE WHEN rd.day_week = '3' THEN rd.reservation END) AS Tuesday,
    MAX(CASE WHEN rd.day_week = '4' THEN rd.reservation END) AS Wednesday,
    MAX(CASE WHEN rd.day_week = '5' THEN rd.reservation END) AS Thursday,
    MAX(CASE WHEN rd.day_week = '6' THEN rd.reservation END) AS Friday,
    MAX(CASE WHEN rd.day_week = '7' THEN rd.reservation END) AS Saturday
FROM AllTimeSlots ats
-- 左连接确保所有时间点都显示，即使无预约
LEFT JOIN ReservationData rd ON ats.HORAINI = rd.HORAINI
-- 按时间分组，确保每个时间点仅一行
GROUP BY ats.HORAINI
-- 按时间排序，符合日历逻辑
ORDER BY ats.HORAINI;

方案2：基于视图中所有唯一时间的转换 #

如果不需要固定时间列表，而是显示视图中出现过的所有时间点，可修改AllTimeSlots CTE为提取视图中的唯一时间：

WITH AllTimeSlots AS (
    -- 提取视图中所有不重复的预约时间
    SELECT DISTINCT CONVERT(VARCHAR(5), T.HORAINI_AULA, 108) AS HORAINI
    FROM vw_turmas_grade T
),
ReservationData AS (
    SELECT 
        CONVERT(VARCHAR(5), T.HORAINI_AULA, 108) AS HORAINI,
        reservation,
        day_week
    FROM vw_turmas_grade T
)
SELECT 
    ats.HORAINI,
    MAX(CASE WHEN rd.day_week = '2' THEN rd.reservation END) AS Monday,
    MAX(CASE WHEN rd.day_week = '3' THEN rd.reservation END) AS Tuesday,
    MAX(CASE WHEN rd.day_week = '4' THEN rd.reservation END) AS Wednesday,
    MAX(CASE WHEN rd.day_week = '5' THEN rd.reservation END) AS Thursday,
    MAX(CASE WHEN rd.day_week = '6' THEN rd.reservation END) AS Friday,
    MAX(CASE WHEN rd.day_week = '7' THEN rd.reservation END) AS Saturday
FROM AllTimeSlots ats
LEFT JOIN ReservationData rd ON ats.HORAINI = rd.HORAINI
GROUP BY ats.HORAINI
ORDER BY ats.HORAINI;

方案说明 #

• AllTimeSlots：提供完整的时间维度，确保无预约的时间点也能显示
• LEFT JOIN：关联时间维度和预约数据，保留所有时间点
• MAX(CASE...)：将同一时间点下不同周几的预约项聚合到对应列，避免多行重复
• GROUP BY：保证每个时间点仅返回一行记录

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