SQL Server实现动态列数行转列：按指定截止月填充0

测试数据 #

DROP TABLE IF EXISTS #test;
CREATE TABLE #test (id NVARCHAR(20), yr CHAR(4), mo CHAR(2), yr_mo CHAR(7), val int);

INSERT INTO
    #test (id, yr, mo, yr_mo, val)
VALUES
    ('bob', '2023', '01', '2023_01', 100),
    ('bob', '2023', '02', '2023_02', 75),
    ('bob', '2023', '03', '2023_03', 0),
    ('bob', '2023', '04', '2023_04', 20),
    ('bob', '2023', '05', '2023_05', 60),
    ('jennifer', '2023', '01', '2023_01', 0),
    ('jennifer', '2023', '02', '2023_02', 10);

需求 #

通过PIVOT将yr_mo的行值转换为列，覆盖从起始月份到指定截止月份的所有月份（示例为2023_08），缺失数据的月份自动填充0。要求实现动态方案：仅需指定截止月份，即可自动生成对应列并填充数据。

期望输出示例 #

DROP TABLE IF EXISTS #desired;
CREATE TABLE #desired (
    id NVARCHAR(20),
    [2023_01] int,
    [2023_02] int,
    [2023_03] int,
    [2023_04] int,
    [2023_05] int,
    [2023_06] int,
    [2023_07] int,
    [2023_08] int -- 可动态指定的截止月份
);

INSERT INTO #desired (
    id,
    [2023_01],
    [2023_02],
    [2023_03],
    [2023_04],
    [2023_05],
    [2023_06],
    [2023_07],
    [2023_08]
)
VALUES
    ('bob', 100, 75, 0, 20, 60, 0, 0, 0),
    ('jennifer', 0,  10,  0, 0,  0,  0, 0, 0);

当前实现的问题 #

现有方案需手动枚举所有月份列，不具备动态性，每次变更截止月份都要修改SQL语句：

SELECT
    id,
    ISNULL(pvt.[2023_01], 0) AS '2023_01', 
    ISNULL(pvt.[2023_02], 0) AS '2023_02',
    ISNULL(pvt.[2023_03], 0) AS '2023_03',
    ISNULL(pvt.[2023_04], 0) AS '2023_04',
    ISNULL(pvt.[2023_05], 0) AS '2023_05',
    ISNULL(pvt.[2023_06], 0) AS '2023_06',
    ISNULL(pvt.[2023_07], 0) AS '2023_07',
    ISNULL(pvt.[2023_08],0) AS '2023_08'
FROM (
    SELECT id, yr_mo, val FROM #test
) AS src
PIVOT (
    SUM(val)
    FOR yr_mo in ([2023_01], [2023_02], [2023_03], [2023_04], [2023_05], [2023_06], [2023_07], [2023_08])
) AS pvt;

动态解决方案 #

以下动态SQL通过生成指定范围内的所有月份列表，自动构造PIVOT所需的列名和查询字段，只需修改@EndYrMo变量即可适配不同截止月份：

DECLARE @EndYrMo CHAR(7) = '2023_08'; -- 指定截止月份
DECLARE @StartDate DATE = DATEFROMPARTS(LEFT(@EndYrMo,4), 1, 1); -- 从当年1月开始，如需自定义起始可修改此处
DECLARE @EndDate DATE = DATEFROMPARTS(LEFT(@EndYrMo,4), RIGHT(@EndYrMo,2), 1);

-- 生成所有需要的yr_mo列表
WITH MonthList AS (
    SELECT @StartDate AS MonthDate
    UNION ALL
    SELECT DATEADD(MONTH, 1, MonthDate)
    FROM MonthList
    WHERE MonthDate <= @EndDate
)
SELECT CONVERT(CHAR(4), YEAR(MonthDate)) + '_' + RIGHT('0' + CONVERT(VARCHAR(2), MONTH(MonthDate)), 2) AS yr_mo
INTO #AllMonths
FROM MonthList;

-- 构造PIVOT的列名（带方括号）
DECLARE @PivotCols NVARCHAR(MAX);
SELECT @PivotCols = STRING_AGG(QUOTENAME(yr_mo), ', ')
FROM #AllMonths;

-- 构造SELECT的字段（带ISNULL补0）
DECLARE @SelectCols NVARCHAR(MAX);
SELECT @SelectCols = STRING_AGG('ISNULL(pvt.' + QUOTENAME(yr_mo) + ', 0) AS ' + QUOTENAME(yr_mo), ', ')
FROM #AllMonths;

-- 构造并执行动态PIVOT SQL
DECLARE @DynamicSQL NVARCHAR(MAX) = N'
SELECT id, ' + @SelectCols + '
FROM (
    SELECT t.id, m.yr_mo, ISNULL(t.val, 0) AS val
    FROM #AllMonths m
    CROSS JOIN (SELECT DISTINCT id FROM #test) ids
    LEFT JOIN #test t ON m.yr_mo = t.yr_mo AND ids.id = t.id
) AS src
PIVOT (
    SUM(val)
    FOR yr_mo IN (' + @PivotCols + ')
) AS pvt;';

EXEC sp_executesql @DynamicSQL;

-- 清理临时表
DROP TABLE #AllMonths;

说明 #

1. 月份范围控制：默认从截止年份的1月开始生成月份列表，如需自定义起始月份，可修改@StartDate变量（例如改为'2022_10'对应的日期）。
2. 自动补全缺失数据：通过CROSS JOIN生成所有id和月份的组合，再LEFT JOIN原表数据，确保缺失的月份数据自动填充0。
3. 动态列生成：利用STRING_AGG函数自动拼接PIVOT所需的列名和查询字段，无需手动枚举。

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