如何格式化SQL Server表数据以生成员工项目分配报表？

针对你提供的test表（对应原Staff Allocation表），需要将横向的月份列转换为纵向的年月维度记录，以下提供三种实用实现方式：

方法一：UNION ALL手动拆分（直观易理解） #

适合月份列数量较少的场景，直接将每个月份列拆分为独立行后合并：

SELECT
    StaffID,
    ProjectID,
    '2020-01' AS YearMonth,
    Jan20 AS Allocation
FROM test
UNION ALL
SELECT
    StaffID,
    ProjectID,
    '2020-02' AS YearMonth,
    Feb20 AS Allocation
FROM test
UNION ALL
SELECT
    StaffID,
    ProjectID,
    '2020-03' AS YearMonth,
    Mar20 AS Allocation
FROM test
UNION ALL
SELECT
    StaffID,
    ProjectID,
    '2020-04' AS YearMonth,
    Apr20 AS Allocation
FROM test
UNION ALL
SELECT
    StaffID,
    ProjectID,
    '2020-05' AS YearMonth,
    May20 AS Allocation
FROM test
UNION ALL
SELECT
    StaffID,
    ProjectID,
    '2020-06' AS YearMonth,
    Jun20 AS Allocation
FROM test
UNION ALL
SELECT
    StaffID,
    ProjectID,
    '2020-07' AS YearMonth,
    Jul20 AS Allocation
FROM test
-- 若有更多月份列，继续添加对应UNION ALL语句
ORDER BY StaffID, ProjectID, YearMonth;

方法二：UNPIVOT运算符（简洁高效） #

利用SQL Server内置的UNPIVOT运算符直接实现列转行，同时处理列名到标准年月格式的转换：

SELECT
    StaffID,
    ProjectID,
    -- 将Jan20这类列名转换为'2020-01'格式的年月标识
    CONCAT('20', RIGHT(MonthCol, 2), '-', 
        CASE LEFT(MonthCol, 3)
            WHEN 'Jan' THEN '01' WHEN 'Feb' THEN '02' WHEN 'Mar' THEN '03' WHEN 'Apr' THEN '04'
            WHEN 'May' THEN '05' WHEN 'Jun' THEN '06' WHEN 'Jul' THEN '07' WHEN 'Aug' THEN '08'
            WHEN 'Sep' THEN '09' WHEN 'Oct' THEN '10' WHEN 'Nov' THEN '11' WHEN 'Dec' THEN '12'
        END) AS YearMonth,
    Allocation
FROM test
UNPIVOT (
    -- 指定要转换的数值列和存储原列名的新列
    Allocation FOR MonthCol IN (Jan20, Feb20, Mar20, Apr20, May20, Jun20, Jul20)
) AS UnpivotResult
ORDER BY StaffID, ProjectID, YearMonth;

方法三：补全年12个月记录（报表完整度要求高） #

如果需要确保每个员工-项目组合都有全年12个月的分配记录（即使值为0），可以通过生成年月维度表后关联实现：

-- 生成2020年12个月的日期维度
WITH YearMonths AS (
    SELECT DATEFROMPARTS(2020, month_num, 1) AS YearMonthDate
    FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) AS Months(month_num)
),
-- 获取所有唯一的员工-项目组合
StaffProjects AS (
    SELECT DISTINCT StaffID, ProjectID
    FROM test
)
SELECT
    sp.StaffID,
    sp.ProjectID,
    FORMAT(ym.YearMonthDate, 'yyyy-MM') AS YearMonth,
    -- 用ISNULL将无数据的月份填充为0
    ISNULL(t.Allocation, 0) AS Allocation
FROM StaffProjects sp
CROSS JOIN YearMonths ym
LEFT JOIN (
    -- 先转换原表数据为行格式
    SELECT
        StaffID,
        ProjectID,
        DATEFROMPARTS(
            2000 + CAST(RIGHT(MonthCol, 2) AS INT),
            CASE LEFT(MonthCol, 3)
                WHEN 'Jan' THEN 1 WHEN 'Feb' THEN 2 WHEN 'Mar' THEN 3 WHEN 'Apr' THEN 4
                WHEN 'May' THEN 5 WHEN 'Jun' THEN 6 WHEN 'Jul' THEN 7 WHEN 'Aug' THEN 8
                WHEN 'Sep' THEN 9 WHEN 'Oct' THEN 10 WHEN 'Nov' THEN 11 WHEN 'Dec' THEN 12
            END,
            1
        ) AS YearMonthDate,
        Allocation
    FROM test
    UNPIVOT (
        Allocation FOR MonthCol IN (Jan20, Feb20, Mar20, Apr20, May20, Jun20, Jul20)
    ) AS UnpivotResult
) t ON sp.StaffID = t.StaffID 
    AND sp.ProjectID = t.ProjectID 
    AND ym.YearMonthDate = t.YearMonthDate
ORDER BY sp.StaffID, sp.ProjectID, ym.YearMonthDate;

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