英文:
Select query dynamic column QUOTENAME MAX aggregate function
问题
DECLARE @var1 NVARCHAR(20)
DECLARE @sql NVARCHAR(255)
SET @var1 = '2018' -- this is the dynamic column, it will produce 2018, 2019, 2020 and so on
SET @sql = 'select MAX(ID) AS ColID, MAX(Name) AS ColName, ' + QUOTENAME(@var1, 'MAX()') + ' from [Table] GROUP BY ColID'
EXEC sp_executesql @stmt = @sql
但是，我遇到了一个错误：
列 'Table.2018' 在选择列表中无效，因为它既不包含在聚合函数中，也不包含在GROUP BY子句中。
英文:
I have dynamic query to show output DISTINCT using MAX() aggregate function fields.
DECLARE @var1 NVARCHAR(20)
DECLARE @sql NVARCHAR(255)
SET @var1 = '2018' -- this is the dynamic column, it will produce 2018, 2019, 2020 and so on
SET @sql = 'select MAX(ID) AS ColID, MAX(Name) AS ColName, ' + QUOTENAME(@var1, 'MAX()') + ' from [Table] GROUP BY ColID'
EXEC sp_executesql @stmt = @sql
but, I get an error:
> Column 'Table.2018' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
答案1
得分: 1
将变量列放入最大函数中：
SET @sql = '
select
MAX(ID) AS ColID,
MAX(Name) AS ColName,
MAX(' + QUOTENAME(@var1) + ')
from [Table]
GROUP BY ColID
'
英文:
Place the variable column into the max function:
SET @sql = '
select
MAX(ID) AS ColID,
MAX(Name) AS ColName,
MAX(' + QUOTENAME(@var1) + ')
from [Table]
GROUP BY ColID
'
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论