问题描述
我有一个包含以下列的表格:
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