Sum of all dynamic columns

Question

Here is what I need to find,

Sum all values present in dynamically generated system columns.

I have found how to generate dynamically generated columns by using following query.

    declare @columnnames nvarchar(max) =  'select COLUMN_NAME FROM  [Powerbireports].INFORMATION_SCHEMA.COLUMNS
    where TABLE_NAME = ''FCL_Pivot'' and column_name like ''%Charges'''

It shows the columns ending with charges.

I have 30+ columns of charges. My data looks like below.

Data

By using this columns I need to perform sum of all values present in 30 columns(using above query) and update into another column named handling.

I tried by using below query.

    declare @dynamicsql nvarchar(max) = N'Update [dbo].[FCL_Pivot] set Handling = (select SUM('+@columnnames+'))'
    exec sp_executesql @dynamicsql

It is showing incorrect syntax near 'select'.

Please help me how to overcome this query or any alternative approach.

Answer 1

First, build a comma-delimited list of all columns ending with 'charges':

DECLARE @column_plus_str NVARCHAR(MAX) 
SELECT @column_plus_str = STUFF((SELECT '+ISNULL(' + QUOTENAME(COLUMN_NAME)+ ', 0)'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'FCL_Pivot' AND COLUMN_NAME LIKE '%charges'
FOR XML PATH('')), 1, 1, '')

Then update Handling. You don't need the SUM aggregate because you aren't grouping, right? Handling = ISNULL([100% Examination Charges], 0)+ISNULL([50% Examination Charges], 0) + ...

DECLARE @dynamicsql NVARCHAR(MAX) = N'Update [dbo].[FCL_Pivot] set Handling = ' + @column_plus_str
EXEC sp_executesql @dynamicsql