问题描述

DECLARE @DynamicColumns VARCHAR(MAX)
SET @DynamicColumns = '[Mon, Oct 31 2016], [Tue, Nov  1 2016], [Wed, Nov  2 2016], [Thu, Nov  3 2016],[Many More Columns Can be Added]'

我有一个临时表，其中包含 FirstName & 列姓.该表还将包含根据传递给存储过程的参数动态生成的列.动态添加的列可以是 10、100 或 2.

I have a temp table that has columns FirstName & LastName. The table will also contain dynamically generated columns depending on the parameters passed to the stored procedure. The dynamically added columns could be 10, 100 or 2.

我可以在上面声明的 @DynamicColumns 变量中访问生成列的名称.

I can access the names of the generated column in the @DynamicColumns variable i have declared above.

列名称采用如上所示的格式，并以逗号 (,) 分隔

The Columns names are in the format shown above and separated by a comma (,)

问题:如果我在下面编写查询，我可以获得我想要的所有行以及所有动态添加的列.

PROBLEM: If i write the query below, i can get all rows i want with all the dynamically added columns.

SELECT * FROM ##TempTable1

但是我如何计算动态添加的列的总和并将其作为每行的额外总计"列返回.

But how can i compute the sum of the dynamically added columns and return it as an extra "Total" column for each row.

我会写类似的东西

SELECT *, [Mon, Oct 31 2016] + [Tue, Nov  1 2016] + [Wed, Nov  2 2016] + [Thu, Nov  3 2016] AS Total
FROM ##TempTable1

但是列将是动态的，所以这行不通.(但我可以访问变量 @DynamicColumns 中的列名)

but the columns are going to be dynamic, so that wouldn't work.(but i have access to the column names in variable @DynamicColumns)

我需要的是类似于下面的内容，但是我如何从变量 @DynamicColumns 中提取我的列名并获得它们的总和?

What i need is something like below but how can i extract my column names from the variable @DynamicColumns and get their sum?

SELECT *, SUM(@DynamicColumns) AS Total -- the Sum of all columns in @DynamicColumns
FROM ##TempTable1

推荐答案

您可以创建新的动态 T-SQL 语句:

You can create new dynamic T-SQL statement:

DECLARE @DynamicSQLStatement NVARCHAR(MAX) = N'
SELECT *, ' + REPLACE(@DynamicColumns, ',', '+') + '  AS Total 
FROM ##TempTable1;'

exec @DynamicSQLStatement

我刚刚看到，列名中有 ,，因此您可以将 ], [ 替换为逗号.

I have just see, that you have , in the column names, so you can replace ], [ instead comma.

REPLACE(@DynamicColumns, '], [', '] + [')