如何在选择查询中获取动态生成列的总行数

问题描述

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, '], [', '] + [')

这篇关于如何在选择查询中获取动态生成列的总行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！