如何对Pivot生成的动态列执行算术运算？

Got it, let's work through this. You're already using dynamic SQL with PIVOT to populate ##GlobalTempForCapExPivotResult where the fiscal year columns are dynamic. Now you need to multiply those dynamic columns by 3.5—either all of them or a subset. Here are two straightforward approaches:

1. Apply Calculation Directly During Pivot (Recommended) #

Instead of inserting raw values first and then updating, you can modify your pivot query to calculate the multiplied values upfront. This saves an extra UPDATE step and is more efficient.

Modified Dynamic Pivot Code: #

-- Step 1: Get dynamic fiscal year column names
DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);

SELECT @cols = STUFF(
    (SELECT ',' + QUOTENAME(FYYear) 
     FROM #TempCapEx 
     GROUP BY FYYear 
     ORDER BY FYYear 
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
    1, 1, ''
);

-- Step 2: Build pivot query with multiplication (handle NULLs with ISNULL)
SET @query = '
SELECT CapitalExpenditureId, ' + 
    -- Replace each column with ISNULL(col, 0)*3.5 to avoid NULL results
    REPLACE(@cols, '[', 'ISNULL([') + '] * 3.5) AS ' + @cols + '
INTO ##GlobalTempForCapExPivotResult 
FROM (
    SELECT CapitalExpenditureId, FYYear, indicatorvalue 
    FROM #TempCapEx
) x 
PIVOT (
    SUM(indicatorvalue) 
    FOR FYYear IN (' + @cols + ')
) p';

EXECUTE (@query);

What this does: #

• Uses ISNULL(column, 0) to ensure NULL values are treated as 0 before multiplying (so you get 0.0000 instead of NULL in results)
• Directly computes value * 3.5 during the pivot and aliases the columns to keep the original fiscal year names
• Inserts the already calculated values into your global temp table

2. Update Existing Temp Table with Dynamic Columns #

If you already have the pivot table populated and need to apply the calculation afterward, you can generate a dynamic UPDATE statement targeting only the fiscal year columns.

Code for Dynamic Update: #

DECLARE @updateCols NVARCHAR(MAX), @updateQuery NVARCHAR(MAX);

-- Get only the fiscal year columns (exclude CapitalExpenditureId)
SELECT @updateCols = STUFF(
    (SELECT ',' + QUOTENAME(name) + ' = ISNULL(' + QUOTENAME(name) + ', 0) * 3.5'
     FROM tempdb.sys.columns 
     WHERE object_id = OBJECT_ID('tempdb..##GlobalTempForCapExPivotResult')
       AND name != 'CapitalExpenditureId'
     ORDER BY name
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
    1, 1, ''
);

-- Build and execute the UPDATE statement
SET @updateQuery = 'UPDATE ##GlobalTempForCapExPivotResult SET ' + @updateCols;
EXECUTE (@updateQuery);

What this does: #

• Queries tempdb.sys.columns to dynamically get all columns in your global temp table except CapitalExpenditureId
• Generates an UPDATE clause that multiplies each fiscal year column by 3.5 (again handling NULLs with ISNULL)
• Runs the dynamic UPDATE to modify the existing values

3. Target Specific Fiscal Year Columns #

If you only want to multiply a subset of fiscal years (e.g., just 2019-2020 and 2020-2021), you can filter the column list when building @cols or @updateCols.

Example for Specific Columns: #

-- For pivot time calculation
SELECT @cols = STUFF(
    (SELECT ',' + QUOTENAME(FYYear) 
     FROM #TempCapEx 
     WHERE FYYear IN ('2019-2020', '2020-2021') -- Filter desired FYs
     GROUP BY FYYear 
     ORDER BY FYYear 
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
    1, 1, ''
);

-- Or for update time
SELECT @updateCols = STUFF(
    (SELECT ',' + QUOTENAME(name) + ' = ISNULL(' + QUOTENAME(name) + ', 0) * 3.5'
     FROM tempdb.sys.columns 
     WHERE object_id = OBJECT_ID('tempdb..##GlobalTempForCapExPivotResult')
       AND name IN ('2019-2020', '2020-2021') -- Filter desired FYs
     ORDER BY name
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
    1, 1, ''
);

Example Result (Using First Approach): #

For your sample data row CapitalExpenditureId = 22150, the 2017-2018 value would become 35200.0000 * 3.5 = 123200.0000 instead of the original 35200.0000.

内容的提问来源于stack exchange，提问作者Arijit Mukherjee