以月份为列更新查询

Question

需要帮助编写查询，以12个月为列的表中实际数据更新预算数据

我不能更改表模式。

表:Budget2021

Create table Budget2021
(
Account int identity,
Jan_Actual int,
Jan_Budget int,
Feb_Actual int,
Feb_Budget int,
Mar_Actual int,
Mar_Budget int,
Apr_Actual int,
Apr_Budget int,
May_Actual int,
May_Budget int,
Jun_Actual int,
Jun_Budget int,
Jul_Actual int,
Jul_Budget int,
Aug_Actual int,
Aug_Budget int,
Sep_Actual int,
Sep_Budget int,
oct_Actual int,
oct_Budget int,
Nov_Actual int,
Nov_Budget int,
Dec_Actual int,
Dec_Budget int,
)
Insert into Budget2021 values (100,200,
300,100,
4000,1000,
50,50,
1000,1000,
2000,2000,
3000,3000,
4000,4000,
5000,5000,
50,50,
1000,1000,
2000,2000)

我必须更新Budget列以匹配前几个月的Actual列。每个月都需要完成一项工作。

例如，在四月，我必须用Actual列更新Jan, Feb, MarBudget列。

在六月，应复制一月，二月，三月，四月和五月

有人能帮我查询一下吗?(t - sql)

Answer 1

如果我猜对了，你可以使用参数month (2 - 13,2 = Feb, 13 = Jan next Year):

Update Budget2021
set Jan_Budget = CASE WHEN @month > 1 THEN Jan_Actual 
ELSE Jan_Budget,
Feb_Budget = CASE WHEN @month > 2 THEN Feb_Actual 
ELSE feb_Budget,
...
Dec_Budget = CASE WHEN @month > 12 THEN Dec_Actual 
ELSE Dec_Budget

Answer 2

declare @Sql as varchar(max)
select @Sql =(
select  distinct 
' ' + left(name, 3) + '_Budget = ' + left(name, 3) + '_Actual,'  AS [text()]
from sys.all_columns
where object_id = OBJECT_ID('Budget2021')
and column_id < (
select min(column_id)
from sys.all_columns
where object_id = OBJECT_ID('Budget2021')
and left(name , 3) =  CONVERT(VARCHAR(3), DATENAME(MM, GETDATE()), 100)
)
and name <> 'Account'
FOR XML PATH ('')
)
Set @Sql = 'update Budget2021 set ' + left( @Sql , len(@Sql) - 1)
execute(@Sql)