問題描述

我有一個(gè)包含以下列的表格:

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.

所有這些列都有值.

我需要將每一列的值設(shè)置為零，但是我知道我需要旋轉(zhuǎn)數(shù)據(jù)然后更新?

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 中轉(zhuǎn)置結(jié)果，然后在 Excel 中開發(fā)您的查詢，但是，我想知道如何在不使用 Excel 的情況下實(shí)現(xiàn)這一點(diǎn).

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 中轉(zhuǎn)置后，我的更新查詢將如下所示:

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 中開發(fā)相同的更新查詢，而不使用 Excel 并考慮以上所有內(nèi)容?

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

推薦答案

使用一個(gè)更新來統(tǒng)治他們所有

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>%')

或者使用動(dòng)態(tài) 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 此處上進(jìn)行測(cè)試

Test on db<>fiddle here

這篇關(guān)于如何透視列并在之后更新透視數(shù)據(jù)的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！