SQLTeam.com | Weblogs | Forums

How to increment column ordinal position in a while function?

tsql
sql2012

#1

Abstract: I have two 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 (using a left join function)

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)

                                   Select @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.


#2

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

#3

Thanks a lot for your answer ! briefly, I have two tables (they have the same rows and columns) but one with stock returns and the other with the respective quintiles of each return. I made a left join to calculate the average of the returns based on the top quintile that's why I am looking for a function that could go through each column an calculate the average (1314 rows and 121 columns)


#4

2nd code: And how can I execute the "Select Statements"?


#5

To execute SQL statements:

If you amke a query that output 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

as per my earlier example then just Cut & paste that SQL to SSMS and run it. That's OK for a one-off, no good for a scheduled job at 2AM !!

If its not a one-off job then construct the SQL statement(s) into an @Variable then you can execute the SQL using:

EXEC (@VariableName)

However, BEWARE of SQL Injection. In particular you need to be very careful if your SQL was constructed using ANY data that came from user-input.

Either way, I suggest you debug the process by just using PRINT (or SELECT if your prefer) to output the generated SQL from your @Variable and then Cut & Paste that into SSMS and run it to test it. First time you will probably have syntax errors - manually fix those in SSMS and once you've got it working fix your code with the changes you had to make. Generate the code again, cut & paste to SSMS, and repeat until it works!

Once it is working properly THEN replace the PRINT statement with the EXEC. In my earlier example you will see that I had the EXEC commented out thus:

PRINT @SQL
-- EXEC (@SQL)

#6

Thank you I got it :smiley: