How to keep column name after sum aggregation in SQL query

I am querying SQL Server where I have multiple columns with sum aggregation. I’m wondering if there is a way to keep column name after doing sum over each of them.

I want to avoid using aliases immediately after the selected column as their numbers and names change over time.

Example of my result:

My query:

DECLARE @SQLQUERY AS NVARCHAR(MAX)

DECLARE @PivotColumns AS NVARCHAR(MAX)
DECLARE @SumColumns as NVARCHAR(MAX)

SELECT @PivotColumns = COALESCE(@PivotColumns + ',','') + QUOTENAME(description)
FROM [example-base].dbo.table

SELECT @SumColumns = 'sum('+Replace(@PivotColumns,',','),sum(')+')'

SET @SQLQUERY = N'SELECT TOP(100) Weeks, Years, Code,'+@SumColumns+ '
FROM [example-base].dbo.table
group by Weeks,Years,Code'

EXECUTE sp_executesql @SQLQUERY

Is there a way to keep column name on which is done sum?

Answer

Be aware of the potential performance issues by building a query with text. Moreover, your result may be not predictable while the number of columns can evolve between two executions.

To add an alias to your columns, you may use this statement :

DECLARE @SumColumns AS nvarchar(MAX);

SELECT @SumColumns = CONCAT(COALESCE(@PivotColumns + ',', ''), 'SUM(', QUOTENAME(description), ') AS ', QUOTENAME(description))
FROM [example-base].dbo.table