求助：如何通过SQL的PIVOT/UNPIVOT实现宽表转长表

嘿，你找对路子啦！UNPIVOT确实是处理这类宽表转窄表需求的完美工具，我来帮你写出符合预期的SQL语句：

基础实现语句 #

SELECT 
    [Pattern Number],
    WorkingDay,
    Day
FROM (
    -- 这里是你原本的查询，获取宽表数据
    SELECT 
        [Pattern Number],
        Sunday,
        Monday,
        Tuesday,
        Wednesday,
        Thursday,
        Friday,
        Saturday
    FROM Access.Pattern_Details 
    WHERE [Pattern Number] = 4
) AS SourceTable
UNPIVOT (
    -- WorkingDay 用来存储原来各星期几列的0/1值
    WorkingDay 
    -- Day 用来存储原来的列名（即星期几的名称）
    FOR Day IN (
        Sunday,
        Monday,
        Tuesday,
        Wednesday,
        Thursday,
        Friday,
        Saturday
    )
) AS UnpivotTable;

优化：按星期顺序排序 #

如果希望结果里的星期几按照自然顺序（周日到周六）排列，可以加上ORDER BY和CASE语句来控制顺序：

SELECT 
    [Pattern Number],
    WorkingDay,
    Day
FROM (
    SELECT 
        [Pattern Number],
        Sunday,
        Monday,
        Tuesday,
        Wednesday,
        Thursday,
        Friday,
        Saturday
    FROM Access.Pattern_Details 
    WHERE [Pattern Number] = 4
) AS SourceTable
UNPIVOT (
    WorkingDay FOR Day IN (
        Sunday,
        Monday,
        Tuesday,
        Wednesday,
        Thursday,
        Friday,
        Saturday
    )
) AS UnpivotTable
ORDER BY 
    CASE Day 
        WHEN 'Sunday' THEN 1
        WHEN 'Monday' THEN 2
        WHEN 'Tuesday' THEN 3
        WHEN 'Wednesday' THEN 4
        WHEN 'Thursday' THEN 5
        WHEN 'Friday' THEN 6
        WHEN 'Saturday' THEN 7
    END;

这个语句执行后，就能得到你想要的结构化结果：每一行对应一个星期几，同时保留Pattern Number和对应的工作日标记（0/1）。

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