Robin_Hcp Robin_Hcp -4 years ago 98
SQL Question

How to increment column ordinal position in a while function?

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


When I print this code it prints the name of the column based on the number I put from 1 to 122.

Code for the loop function:

WHILE ....
BEGIN
SET @SQL = 'SELECT AVG(MR.[' + @column1 + ']) From MOMENTUM_Quintile MQ
Left Join MOMENTUM_Returns MR on MQ.Mnemonic = MR.Mnemonic WHERE MQ.[' + @column1 + '] = 1'
END


But I definitely do not have a clue how to integrate this function into the loop so the function goes through all the columns one by one, therefore looking for some advice..

Thank you!

R.H.

enter image description here

Answer Source

Can you not just move your SELECT from 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...

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download