SQL变量分组依据和列名

I'm working on some very simple SQL queries, that are twins of the same query, changing a field. Here an example:

我正在研究一些非常简单的SQL查询，即同一查询的双胞胎，更改字段。这是一个例子：

select
field1
,sum(field2)
from table
group by field1

And I have to do this for three fields, summing always field2. this means for example:

我必须为三个领域做到这一点，总是总和field2。这意味着例如：

select
field3
,sum(field2)
from table
group by field3

And so on. I get bored fastly, so I decided to learn something new, applying a variable, and changing the name of the grouping field only once.

等等。我很快就感到无聊，所以我决定学习新东西，应用变量，只更改一次分组字段的名称。

   declare @field varchar (10);
-- use field1, field3, field4
   set @field = 'field1';

select
@field
,sum(field2)
from table
group by @field

And the result is this error:

结果就是这个错误：

Each GROUP BY expression must include at least one column that is not an external reference (roughly translated by Google Translator)

每个GROUP BY表达式必须包含至少一个不是外部引用的列（由Google Translator粗略翻译）

Well, I decided to face the group by in the end, so I removed it:

好吧，我决定最后面对这个小组，所以我把它删除了：

   declare @field varchar (10);
-- use field1, field3, field4
   set @field = 'field1';

select
@field
from table

The result is a column that has no name, and on the rows there are many field1 word as the number of the row, all equals constants, something like:

结果是一个没有名称的列，并且在行上有许多field1字作为行的编号，所有等于常量，如：

no name
field1
field1
field1
....
field1

instead of

代替

field1
a
b
c
...
z

So the problems are two:

所以问题是两个：

• how to add a variable as a column name in a query?
• 如何在查询中添加变量作为列名？
• how to manage it in a group by?
• 如何在一组中管理它？

Thank you in advance for your time!

提前感谢您的时间！

1 个解决方案

#1

This can be done dynamically:

这可以动态完成：

declare @sql as nvarchar(max)
declare @field1 varchar (10);
declare @field2 varchar (10);
set @field1 = 'field1';
set @field2 = 'field2';

set @sql = 'select [' + @field1 + '], sum([' + @field2 + ']) from table group by [' + @field1 + ']'
exec(@sql)