如何透视列并在之后更新透视数据

问题描述

我有一个包含以下列的表格:

I have a table with the following columns:

GLLink int
Budget01 float
Budget02 float
Budget03 float
Budget04 float
Budget05 float
Budget06 float
Budget07 float
...
Budget57 float
Budget58 float
Budget59 float
Budget60 float

由于我的过滤器，每列有 8 行.

For each column, there is 8 rows because of my filter.

所有这些列都有值.

我需要将每一列的值设置为零，但是我知道我需要旋转数据然后更新?

I need to set each columns value to zero, however I have the understanding that I would need to pivot the data and then update afterwards?

我知道您可以在 Excel 中转置结果，然后在 Excel 中开发您的查询，但是，我想知道如何在不使用 Excel 的情况下实现这一点.

I know you can just transpose the results in Excel and then develop you query in Excel, however, I would like to know how to achieve this without using Excel.

在 Excel 中转置后，我的更新查询将如下所示:

After the transpose in Excel, my update query would look like this:

update Budgets set Budget01 = 0 where GLLink in (select AccountLink from Accounts where Master_Sub_Account like '3200>%')
update Budgets set Budget02 = 0 where GLLink in (select AccountLink from Accounts where Master_Sub_Account like '3200>%')
update Budgets set Budget03 = 0 where GLLink in (select AccountLink from Accounts where Master_Sub_Account like '3200>%')
update Budgets set Budget04 = 0 where GLLink in (select AccountLink from Accounts where Master_Sub_Account like '3200>%')
update Budgets set Budget05 = 0 where GLLink in (select AccountLink from Accounts where Master_Sub_Account like '3200>%')
update Budgets set Budget06 = 0 where GLLink in (select AccountLink from Accounts where Master_Sub_Account like '3200>%')
update Budgets set Budget07 = 0 where GLLink in (select AccountLink from Accounts where Master_Sub_Account like '3200>%')
...
update Budgets set Budget57 = 0 where GLLink in (select AccountLink from Accounts where Master_Sub_Account like '3200>%')
update Budgets set Budget58 = 0 where GLLink in (select AccountLink from Accounts where Master_Sub_Account like '3200>%')
update Budgets set Budget59 = 0 where GLLink in (select AccountLink from Accounts where Master_Sub_Account like '3200>%')
update Budgets set Budget60 = 0 where GLLink in (select AccountLink from Accounts where Master_Sub_Account like '3200>%')

我如何需要在 SQL 中开发相同的更新查询，而不使用 Excel 并考虑以上所有内容?

How would I need to develop the same update query in SQL, without using Excel and taking all of the above in consideration?

推荐答案

使用一个更新来**他们所有

Use the One Update To Rule Them All

update Budgets 
set 
Budget01 = 0,
Budget02 = 0,  
Budget03 = 0,  
...
Budget59 = 0,
Budget60 = 0
where GLLink in (select AccountLink from Accounts where Master_Sub_Account like '3200>%')

或者使用动态 SQL

Or use Dynamic SQL

示例:

declare @Cols NVARCHAR(max);
declare @DynSql NVARCHAR(max);

SELECT @Cols = concat(@Cols+', ',char(10), Col.Name, '=0')
FROM SYS.OBJECTS Obj
JOIN SYS.COLUMNS Col ON Obj.OBJECT_ID = Col.OBJECT_ID
WHERE Obj.TYPE='U' 
  AND Obj.NAME = 'Budgets'
  AND Col.Name LIKE 'Budget[0-9][0-9]';

set @DynSql = N'update Budgets set '+ @Cols + char(10) +
'where GLLink in (select AccountLink from Accounts where Master_Sub_Account like ''3200>%'')';

exec(@DynSql);

在db<>fiddle 此处上进行测试

Test on db<>fiddle here