如何透视列并在之后更新透视数据
我有一个包含以下列的表格:
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
相关文章