Abstract: I have a table with multiple columns and I am trying to implement a while (loop) function to all the columns to pull out the average of each column.
I coded this to attribute a number to each column (to ease the possibility of an incremental method)
Declare @tablename as varchar(128)
Declare @column1 as varchar(128)
Delect @tablename = 'MOMENTUM_Results'
Select @column1 = sc.name
from sysobjects as so inner join syscolumns as sc on so.id = sc.id
where so.name = @tablename and sc.colid = 2
SET @SQL = 'SELECT AVG(MR.[' + @column1 + ']) From MOMENTUM_Quintile MQ
Left Join MOMENTUM_Returns MR on MQ.Mnemonic = MR.Mnemonic WHERE MQ.[' + @column1 + '] = 1'
Can you not just move your
sys.Columns into the body of your
WHILE loop to get the one you're after? Something like this...
DECLARE @TABLENAME AS VARCHAR(128); DECLARE @COLUMN1 AS VARCHAR(128); DECLARE @COUNTER INT; SET @TABLENAME = 'MOMENTUM_Results'; SET @COUNTER = 1; WHILE .... BEGIN SELECT @COLUMN1 = C.Name FROM sys.Columns C WHERE OBJECT_NAME(C.object_id) = @TABLENAME AND C.column_id = @COUNTER ; SET @SQL = 'SELECT AVG(MR.[' + @COLUMN1 + ']) From MOMENTUM_Quintile MQ Left Join MOMENTUM_Returns MR on MQ.Mnemonic = MR.Mnemonic WHERE MQ.[' + @COLUMN1 + '] = 1'; ... Other Stuff ... SET @COUNTER = @COUNTER + 1; END ;
Maybe I didn't properly understand your question, but if you already have everything written out you could just change the placement to select the column names you want.
Of course you can also add additional conditions to make sure you're getting matched columns, or columns by name, or whatever...