如何在选择查询中获取动态生成列的总行数
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, '], [', '] + [')
相关文章