在SQL Server与SQLite中实现UNPIVOT并生成TimecardDate列的问询

SQL Server 解决方案 #

SQL Server原生支持UNPIVOT，只需在现有查询基础上添加TimecardDate列，通过CASE语句匹配星期名称确定日期偏移量，再用DATEADD函数计算目标日期：

SELECT 
    DayOftheWeek,
    ActualHrs,
    WeekStartsOn,
    DATEADD(day, 
        CASE DayOftheWeek
            WHEN 'Monday' THEN 1
            WHEN 'Tuesday' THEN 2
            WHEN 'Wednesday' THEN 3
            WHEN 'Thursday' THEN 4
            WHEN 'Friday' THEN 5
        END, 
        WeekStartsOn) AS TimecardDate
FROM (
    SELECT Monday, Tuesday, Wednesday, Thursday, Friday, WeekStartsOn
    FROM itplanning.enriched.timecard_enriched_current
) AS cp 
UNPIVOT ( 
    ActualHrs FOR DayOftheWeek IN (Monday, Tuesday, Wednesday, Thursday, Friday) 
) AS PivotTable
WHERE ActualHrs <> 0

逻辑说明：用CASE映射星期与天数偏移的对应关系，通过DATEADD将偏移量叠加到WeekStartsOn上，生成符合要求的TimecardDate。

SQLite 解决方案 #

SQLite无内置UNPIVOT函数，需用UNION ALL模拟列转行操作，同时在每个分支中直接计算对应日期：

SELECT 
    'Monday' AS DayOftheWeek,
    Monday AS ActualHrs,
    WeekStartsOn,
    DATE(WeekStartsOn, '+1 day') AS TimecardDate
FROM itplanning.enriched.timecard_enriched_current
WHERE Monday <> 0

UNION ALL

SELECT 
    'Tuesday' AS DayOftheWeek,
    Tuesday AS ActualHrs,
    WeekStartsOn,
    DATE(WeekStartsOn, '+2 day') AS TimecardDate
FROM itplanning.enriched.timecard_enriched_current
WHERE Tuesday <> 0

UNION ALL

SELECT 
    'Wednesday' AS DayOftheWeek,
    Wednesday AS ActualHrs,
    WeekStartsOn,
    DATE(WeekStartsOn, '+3 day') AS TimecardDate
FROM itplanning.enriched.timecard_enriched_current
WHERE Wednesday <> 0

UNION ALL

SELECT 
    'Thursday' AS DayOftheWeek,
    Thursday AS ActualHrs,
    WeekStartsOn,
    DATE(WeekStartsOn, '+4 day') AS TimecardDate
FROM itplanning.enriched.timecard_enriched_current
WHERE Thursday <> 0

UNION ALL

SELECT 
    'Friday' AS DayOftheWeek,
    Friday AS ActualHrs,
    WeekStartsOn,
    DATE(WeekStartsOn, '+5 day') AS TimecardDate
FROM itplanning.enriched.timecard_enriched_current
WHERE Friday <> 0

逻辑说明：SQLite的DATE函数支持直接通过字符串格式的偏移参数（如'+1 day'）计算日期，每个UNION ALL分支对应原表的一列，同步生成星期名称和目标日期。

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