I recommend that you don't do
... MR.[' + @column1 + '] ...
to put "[" ... "]" around the column names. I know it is long odds, but a column name that, itself, included a square-bracket wouldn't survive that! This is better:
... MR.' + QuoteName(@column1) + ' ...
Doing each column one-by-one, in a loop, will be very slow.
it would be better to "construct" the SQL to average all columns in a single statement - it would look something like
SELECT AVG(MR.[MyColumn1]) AS [AVG_MyColumn1], AVG(MR.[MyColumn2]) AS [AVG_MyColumn2], ...
FROM MOMENTUM_Quintile AS MQ
LEFT JOIN ...
That SQL statement could be constrcuted with something like this
DECLARE @tablename varchar(128) = 'MOMENTUM_Results'
DECLARE @SQL NVarchar(MAX)
SELECT @SQL = COALESCE(@SQL+ ', ', '')
+ 'AVG(MR.' + QuoteName(C.name) + ') AS ' + QuoteName(C.name)
FROM sys.objects AS O
join sys.columns AS C
ON C.object_id = O.object_id
WHERE O.name = @tablename
AND C.column_id >= 2
ORDER BY O.name, C.column_id
SELECT @SQL = 'SELECT ' + @SQL
+ ' FROM MOMENTUM_Quintile AS MQ LEFT JOIN MOMENTUM_Returns AS MR on MQ.Mnemonic = MR.Mnemonic WHERE MQ.SomeColumn = 1'
PRINT @SQL
-- EXEC (@SQL)
However, your code appears to be only included values, for the average, where the Joined Table has a specific value.
'SELECT AVG(MR.[' + @column1 + ']) From ... WHERE MQ.[' + @column1 + '] = 1'
First point is that I don't see the point of the LEFT JOIN here, no point trying to AVERAGE values including non-existent ones from that JOIN (you'll get a warning message if there are any rows in MQ that have no row in MR), so you probably want an Inner Join
If you want to do it in one pass its a bit more complicated. You basically need to do:
SUM(CASE WHEN MQ.SomeColumn =1 THEN MR.SomeColumn ELSE 0 END) AS MyTotal
, SUM(CASE WHEN MQ.SomeColumn =1 THEN 1 ELSE 0 END) AS MyCount
and the construct the average as
MyTotal / NullIf(MyCount, 0) AS MyAverage
but you could still do this for all columns in one pass.
SQL works much MUCH faster doing things in SETs rather than row-by-agonising-row (RBAR) using loops. This solution, doing all columns in one pass, could easily be 100x faster than a loop.
Now, if its just a one off, and the data volume is not huge, it may not matter - so whatever is easiest to program will do.
One other way of doing it is like this:
DECLARE @tablename varchar(128) = 'MOMENTUM_Results'
SELECT 'SELECT ''' + C.name + ''', AVG(MR.' + QuoteName(C.name) + ')'
+ ' FROM MOMENTUM_Quintile AS MQ LEFT JOIN MOMENTUM_Returns AS MR on MQ.Mnemonic = MR.Mnemonic'
+ ' WHERE MQ.' + QuoteName(C.name) + ' = 1'
FROM sys.objects AS O
join sys.columns AS C
ON C.object_id = O.object_id
WHERE O.name = @tablename
AND C.column_id >= 2
ORDER BY O.name, C.column_id
that will generate a whole lot of SELECT statements, and you can then just EXECUTE that. Works well if this is a one-off. The output from that statement would generate something like this
SELECT 'MyColumn1', AVG(MR.[MyColumn1]) FROM MOMENTUM_Quintile AS MQ LEFT JOIN MOMENTUM_Returns AS MR on MQ.Mnemonic = MR.Mnemonic WHERE MQ.[MyColumn1] = 1
SELECT 'MyColumn2', AVG(MR.[MyColumn2]) FROM MOMENTUM_Quintile AS MQ LEFT JOIN MOMENTUM_Returns AS MR on MQ.Mnemonic = MR.Mnemonic WHERE MQ.[MyColumn2] = 1
