如何修改SQL手动DDL脚本以动态创建带年份透视列的全局临时表

问题背景 #

你现在需要把手动编写的DDL脚本改造为能自动生成2017年至当前年份的注册/注销日期列的版本，实现年份透视的效果——后续运行脚本时，不用手动修改列定义，就能自动适配最新年份（比如2022、2023年）。原来的手动DDL如下：

DROP TABLE IF EXISTS ##Enrollment 
CREATE TABLE ##Enrollment ( 
[ID] VARCHAR(10), 
[First_Name] VARCHAR(255) NULL , 
[Middle_Name] VARCHAR(255) NULL, 
[Last_Name] VARCHAR(255) NULL, 
[Date_of_Birth] DATE NULL, 
[2017_Enrollment_Date] DATE NULL, 
[2017_Disenrollment_Date] DATE NULL, 
[2018_Enrollment_Date] DATE NULL, 
[2018_Disenrollment_Date] DATE NULL, 
[2019_Enrollment_Date] DATE NULL, 
[2019_Disenrollment_Date] DATE NULL, 
[2020_Enrollment_Date] DATE NULL, 
[2020_Disenrollment_Date] DATE NULL, 
[2021_Enrollment_Date] DATE NULL, 
[2021_Disenrollment_Date] DATE NULL, 
[Begin_Date] DATE NULL, 
[End_Date] DATE NULL 
)

技术方向与实现方法 #

先说结论：WHILE循环是完全合适的方案，逻辑清晰、容易维护；当然也有更简洁的无循环方案，下面分别给你详细说明：

1. WHILE循环 + 动态SQL拼接（易理解，好维护） #

这个思路很直接：先定义起始年份和当前年份，然后通过循环逐个拼接每年的列定义，最后把拼接好的内容插入到完整的CREATE TABLE语句里执行。

示例代码：

-- 定义年份范围：起始年2017，当前年取系统当前年份
DECLARE @StartYear INT = 2017
DECLARE @CurrentYear INT = YEAR(GETDATE())
DECLARE @YearColumns NVARCHAR(MAX) = ''

-- 循环拼接每一年的注册/注销日期列
WHILE @StartYear <= @CurrentYear
BEGIN
    SET @YearColumns += CONCAT(
        '[', @StartYear, '_Enrollment_Date] DATE NULL, ',
        '[', @StartYear, '_Disenrollment_Date] DATE NULL, '
    )
    SET @StartYear += 1
END

-- 拼接完整的建表SQL语句
DECLARE @CreateTableSQL NVARCHAR(MAX) = CONCAT(
    'DROP TABLE IF EXISTS ##Enrollment;
    CREATE TABLE ##Enrollment (
        [ID] VARCHAR(10),
        [First_Name] VARCHAR(255) NULL,
        [Middle_Name] VARCHAR(255) NULL,
        [Last_Name] VARCHAR(255) NULL,
        [Date_of_Birth] DATE NULL,
        ', @YearColumns, '
        [Begin_Date] DATE NULL,
        [End_Date] DATE NULL
    )'
)

-- 执行动态生成的SQL
EXEC sp_executesql @CreateTableSQL

2. 更优方案：用数字序列生成年份列（无循环，性能更好） #

如果想避免循环，可以利用系统自带的数字序列或者递归CTE生成年份列表，再用STRING_AGG（SQL Server 2017+支持）拼接列定义，代码更简洁，当年份范围大的时候性能也更优。

示例代码（适用于SQL Server 2017及以上版本）：

-- 生成2017到当前年份的列表，自动拼接列定义
DECLARE @YearColumns NVARCHAR(MAX)
SELECT @YearColumns = STRING_AGG(
    CONCAT('[', YearNum, '_Enrollment_Date] DATE NULL, [', YearNum, '_Disenrollment_Date] DATE NULL'),
    ', '
)
FROM (
    -- 生成从2017到当前年的数字序列
    SELECT 2017 + n AS YearNum
    FROM (SELECT TOP (YEAR(GETDATE()) - 2017 + 1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
          FROM master..spt_values) t
) YearList

-- 拼接并执行建表语句
DECLARE @CreateTableSQL NVARCHAR(MAX) = CONCAT(
    'DROP TABLE IF EXISTS ##Enrollment;
    CREATE TABLE ##Enrollment (
        [ID] VARCHAR(10),
        [First_Name] VARCHAR(255) NULL,
        [Middle_Name] VARCHAR(255) NULL,
        [Last_Name] VARCHAR(255) NULL,
        [Date_of_Birth] DATE NULL,
        ', @YearColumns, ',
        [Begin_Date] DATE NULL,
        [End_Date] DATE NULL
    )'
)

EXEC sp_executesql @CreateTableSQL

额外注意点 #

• 动态SQL安全：这里的年份是通过系统函数生成的固定数值，不存在SQL注入风险，不用额外做防注入处理
• 临时表特性：全局临时表（##开头）会在所有使用它的会话关闭后自动删除，如果需要持久化数据，可以改成普通表或者本地临时表（#开头）
• 追加列需求：如果不是每次重建表，而是要给已存在的表追加新年份的列，可以把逻辑改成拼接ALTER TABLE ##Enrollment ADD [年份_XXX_Date] DATE NULL语句，记得先判断列是否存在再执行，避免报错

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