Dynamic User-Defined Columns - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic User-Defined Columns

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-09-02 : 06:53:10

Alex writes "Dear SQLGuru,

I am trying to implement a dynamic query whereby the columns output are calculations pre-selected by the user. For example: the user says that for a particular transaction type, columns 1 & 3 should be multipled together, whereas for another transaction type, it should be columns 1 & 4.

I was thinking of hard wiring the possible column combinations into a table and then have the user associate the particular combination with each transaction type, although this sort of fixes things a bit too much. I thought a good idea would be to build the math part dynamically, loading the respective sides of the calculation from the database, but cant seem to work out how to load column names from a table and then use the name in a query.

Hope you have experience of this! Thanks!"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-02 : 12:09:13

If it is always multiplication, and always a fixed list of columns, look at this:


create procedure ReturnCalc(@Col1 int, @col2 int, @col3 int)
' pass in a 1 to include the column in the calculation
' pass in a 0 to exclude that column

as
select A.*, case when @col1 = 0 then 1 else @col1 end *
            case when @col2 = 0 then 1 else @col2 end *
            case when @col3 = 0 then 1 else @col3 end as Result
from
  SomeTable A

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources