求根据起始日期动态生成年月列的SQL脚本解决方案

需求说明 #

我想要构建一个可根据输入的起始日期动态生成列的表格。

源表结构 #

期望输出表结构 #

我尝试过使用CTE但未成功，恳请帮忙编写对应的SQL脚本。

解决方案 #

由于SQL本身是静态语言，动态生成列需要借助动态SQL实现，以下提供两种主流数据库的实现方案：

方案一：SQL Server 实现 #

DECLARE @StartDate DATE = '2024-01-28';
DECLARE @Recurrences INT = (SELECT [no of recurrences] FROM YourTableName WHERE Income# = 1);
DECLARE @DynamicColumns NVARCHAR(MAX) = '';
DECLARE @SQL NVARCHAR(MAX);

-- 生成动态列名（YYYY-MM格式）
WITH DateSequence AS (
    SELECT 1 AS Seq, FORMAT(DATEADD(MONTH, 0, @StartDate), 'yyyy-MM') AS MonthName
    UNION ALL
    SELECT Seq + 1, FORMAT(DATEADD(MONTH, Seq, @StartDate), 'yyyy-MM')
    FROM DateSequence
    WHERE Seq < @Recurrences
)
SELECT @DynamicColumns += QUOTENAME(MonthName) + ' INT, '
FROM DateSequence;

-- 移除末尾多余的逗号和空格
SET @DynamicColumns = LEFT(@DynamicColumns, LEN(@DynamicColumns) - 2);

-- 构建最终SQL语句
SET @SQL = N'
SELECT 
    Income#,
    [income start date],
    ' + @DynamicColumns + '
FROM (
    SELECT 
        Income#,
        [income start date],
        [recurrent income],
        FORMAT(DATEADD(MONTH, Seq - 1, @StartDate), ''yyyy-MM'') AS MonthName
    FROM YourTableName
    CROSS APPLY (
        SELECT TOP([no of recurrences]) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Seq
        FROM sys.all_columns
    ) AS SeqTable
) AS SourceData
PIVOT (
    SUM([recurrent income])
    FOR MonthName IN (' + REPLACE(@DynamicColumns, ' INT, ', ', ') + ')
) AS PivotTable;';

-- 执行动态SQL
EXEC sp_executesql @SQL, N'@StartDate DATE', @StartDate = @StartDate;

方案二：MySQL 实现 #

SET @StartDate = '2024-01-28';
SET @Recurrences = (SELECT `no of recurrences` FROM YourTableName WHERE `Income#` = 1);
SET @DynamicColumns = '';
SET @SQL = '';

-- 生成动态列名和PIVOT部分
SET @i = 0;
WHILE @i < @Recurrences DO
    SET @MonthName = DATE_FORMAT(DATE_ADD(@StartDate, INTERVAL @i MONTH), '%Y-%m');
    SET @DynamicColumns = CONCAT(@DynamicColumns, IF(@i > 0, ', ', ''), '`', @MonthName, '`');
    SET @i = @i + 1;
END WHILE;

-- 构建最终SQL
SET @SQL = CONCAT('
SELECT 
    `Income#`,
    `income start date`,
    ', @DynamicColumns, '
FROM (
    SELECT 
        `Income#`,
        `income start date`,
        `recurrent income`,
        DATE_FORMAT(DATE_ADD(@StartDate, INTERVAL (Seq - 1) MONTH), ''%Y-%m'') AS MonthName
    FROM YourTableName
    CROSS JOIN (
        SELECT @row := @row + 1 AS Seq FROM 
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t1,
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t2,
        (SELECT @row := 0) t0
    ) AS SeqTable
    WHERE Seq <= `no of recurrences`
) AS SourceData
PIVOT (
    SUM(`recurrent income`)
    FOR MonthName IN (', @DynamicColumns, ')
) AS PivotTable;');

-- 执行动态SQL
PREPARE stmt FROM @SQL;
EXECUTE stmt USING @StartDate;
DEALLOCATE PREPARE stmt;

注意事项 #

1. 替换代码中的YourTableName为实际的源表名称；
2. 动态列的数量由no of recurrences字段决定，从起始日期开始逐月生成；
3. 若需要支持多笔收入记录，只需调整动态SQL中的数据筛选逻辑即可。

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