hscast
asked on
Aggregate function on a column supplied by a variable
Here is the dilemma. I have a stored procedure where I need to grab the AVG of a column containing chemistry data. There is a separate column for all chemistries we track, roughly 26 of them. C, ZR, Pb, etc. etc. This stored procedure needs to just do the avg on the chemistry that the user selects.
Is there a way to do this dynamically by passing the column name as a variable?
Below is a version that I attempted, but I get the error:
The name 'SELECT TOP 1 Avg[C] FROM XXXX GROUP BY Type' is not a valid identifier.
Is there a way to do this dynamically by passing the column name as a variable?
Below is a version that I attempted, but I get the error:
The name 'SELECT TOP 1 Avg[C] FROM XXXX GROUP BY Type' is not a valid identifier.
DECLARE @SQL2 varchar(1000)
BEGIN
DECLARE @in nvarchar(5)
SET @in = 'C'
SET @SQL2 = 'SELECT TOP 1 Avg['+@in+'] FROM XXXX GROUP BY Type'
EXEC @SQL2
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Whoops. Quick answer Forefront! I'll give you the points still.
ASKER