Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tolberjj
Starting Member
1 Post |
 Posted - 2007-07-19 : 13:31:32
|
| This has proved difficult to search for. Any help is greatly appreciated.I have a table with approx 150 columns of numeric data type. I am trying to write a stored procedure that average's a single column from two+ rows and insert the result into another table.I would like to place the column names in a single column table, and using a cursor, recursively go through each column so I only have 1 select statement and 1 insert statement in the stored procedure that is called 150 times, rather than 300 unique statements. This is very easy on the VB side, but I am struggling with it on the SQL side.A non-working example is below, but it should give the point.declare @ColName as varchar(50)set @ColName = 'interest_income'select avg(@ColName) from history where user_id = 111275Thanks |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-07-19 : 13:49:44
|
| You would need to use dynamic SQL.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
kvinlim2000
Starting Member
2 Posts |
Posted - 2007-07-30 : 23:48:35
|
| I ran into the same problem this morning. Brief explanation to my table structure. I have 24 fields to keep 2 years of monthly points amount. So, each month, i need to update into the relevant columns so i was thinking of using a variable to control the 24 months field name. It couldn't update and therefore i resolved it by writing a stored procedure to write to EACH and EVERY field separately based on the field name i filtered out first. The sample code is below.i'm not good with words but generally you'll get the idea.IF(@STRYEAR = @WKYEAR) BEGIN SET @TEMP1 = @STRMONTH -- START MONTH SET @TEMP2 = @WKMONTH -- CURRENT MONTH SET @MM = (@TEMP2 - @TEMP1) + 1 IF @MM < 10 SET @MM = '0' ++ @MM -- GET THE FIELD NAME SET @FIELD_NAME = @PA ++ @MM ++ @PTS -- UPDATES THE POINTS TABLE SET @STR_SP = 'SP' ++ '_' ++ 'UPD' ++ '_' ++ @FIELD_NAME EXEC @STR_SP -- This SP is based on the field name filtered above. @SUPPID = @SUPPID, @AMOUNT = @POINTS ENDHope it helps |
|
|
|
kvinlim2000
Starting Member
2 Posts |
Posted - 2007-07-30 : 23:51:09
|
| I ran into the same problem this morning. Brief explanation to my table structure. I have 24 fields to keep 2 years of monthly points amount. So, each month, i need to update into the relevant columns so i was thinking of using a variable to control the 24 months field name. It couldn't update and therefore i resolved it by writing a stored procedure to write to EACH and EVERY field separately based on the field name i filtered out first. The sample code is below.i'm not good with words but generally you'll get the idea.IF(@STRYEAR = @WKYEAR) BEGIN SET @TEMP1 = @STRMONTH -- START MONTH SET @TEMP2 = @WKMONTH -- CURRENT MONTH SET @MM = (@TEMP2 - @TEMP1) + 1 IF @MM < 10 SET @MM = '0' ++ @MM -- GET THE FIELD NAME SET @FIELD_NAME = @PA ++ @MM ++ @PTS -- UPDATES THE POINTS TABLE SET @STR_SP = 'SP' ++ '_' ++ 'UPD' ++ '_' ++ @FIELD_NAME EXEC @STR_SP -- This SP is based on the field name filtered above. @SUPPID = @SUPPID, @AMOUNT = @POINTS ENDHope it helps |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2007-07-31 : 09:26:25
|
| http://en.wikipedia.org/wiki/Database_normalization[Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQLhttp://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
Stripeman
Starting Member
1 Post |
Posted - 2008-08-12 : 04:17:20
|
| This is exactly what you were looking for...http://extjs.com/forum/showthread.php?t=43843 |
|
|
|
|
|
|
|
|
Using variable for column name in select statement