创建SQL全局温度表，同时在日期范围之间以列动态地轮换年份

Question

如何修改下面的手动DDL脚本，以动态创建表呢?例如，我想为2017年和当前年份(2021年)之间的每一年创建2列(注册年份和注销年份)，并对它们进行pivot。目前，该脚本将手动创建从2017年到2021年的列。我应该采取什么方向来创造这种动态?

我正在考虑使用while循环来完成这一点，但我不太确定我是否在正确的轨道上，或者是否有更好的方法。明年2022年脚本运行时，它应该附加[2022]列。明年明年2023脚本应该附加[2023]列，等等。

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
)

Answer 1

一种方法是在最大值和最小值之间迭代年份，然后迭代动态列的两个可能名称

declare @minyear int = 2017
declare @maxyear int
declare @ddl varchar(max)
declare @i int = 0
if (@maxyear is null) select @maxyear=year(getdate())
drop table if exists ##Enrollment
set @ddl = 
'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
,[Begin_Date]                   DATE NULL
,[End_Date]                     DATE NULL'
while @maxyear >= @minyear
begin    
while @i <= 1
begin 
set @ddl = @ddl + char(13) + 
',[' + cast(@minyear as varchar(4)) + '_' + case when @i = 0 then 'En' else 'Disen' end + 'rollment_Date] DATE NULL'
set @i = @i + 1
end

set @minyear = @minyear + 1
set @i = 0
end 
set @ddl = @ddl + char(13) + ')'
exec (@ddl)

如果您将值设置为@maxyear(例如在声明中，使用declare @maxyear int = 2023)，则可以检查列是否达到2023年。

您可以在此数据库<>提琴

上进行测试。