如何透视列并在之后更新透视数据-尊龙凯时

问题描述

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

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