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 |
|
lampis
Starting Member
10 Posts |
 Posted - 2011-09-16 : 06:57:22
|
| Hello,I have a problem with multiplying columns in one of my tables with another column from another table.This is some example code (VBA) to illustrate my problem:"SELECT [T-Bills]*[USD],[Treasuries]*[USD],[Credit]*[USD],[Equities]*[USD]" & _" ,[Commodities]*[USD] FROM [" & ds & "] ds " & _" INNER JOIN [CPI] cpi ON ds.date = cpi.[date]"Where [" & ds & "] is the table that consist of the asset classes.As you can see, I multiply my asset returns with the US CPI.Now I have to write out all the names of the columns and multiply all of them with US CPI. I want instead to be able to select the entire table and then multiply that selection with US CPI. Then I don't need to manually write all column namnes in the code.Something like this:SELECT * FROM [" & ds & "]*[USD] INNER JOIN [CPI] ON ds.date = cpi.dateThis code does not work, I know, but how would I go about to make something like this that would work?Thanks in advance./lampis |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
lampis
Starting Member
10 Posts |
Posted - 2011-09-16 : 07:29:29
|
quote:
Originally posted by tkizer
Just how many columns are in your table that writing them out is a problem? You can even use SSMS to generate the code for you.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Well, I want to be able to change to another table, which means that there will be other column names. In that way I can make my code more dynamical. As it is now, i need to make an if statement (checking what table I want) and different sql codes for the particular table.Im working in Excel with VBA code, thats why I don't use SSMS |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
lampis
Starting Member
10 Posts |
Posted - 2011-09-16 : 07:44:06
|
quote:
Originally posted by tkizer
It sounds like you need to fix your table design. Could you go into more detail about your data/business requirements?SSMS is Management Studio, a SQL client tool.You can achieve this via dynamic SQL, but at the cost of performance and security. Oh and readability.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Well, my business have different data sets of asset class returns. What I just showed you is global assets and the number of column and names vary between different data sets. In each column there is monthly returns of the asset classes. The data is then imported from Excel via VBA code. So sometimes one want to look at global returns, sometimes asian returns etc. Also, I want to be able to have it in real or nominal returns (hence multiplying with US CPI), or in different currencies etc. The problem for me is to make my VBA code as dynamic as possible. It will need tonnes of if statements if I am going to write all column names etc manually.Don't know what dynamic SQL, but I will look into it. |
|
|
|
|
|
|
|
|
Multiply all columns in a table with other column