Replace column with parameter

[johnhugh]

Hi,

I'm using SQL 2005.
Is it possible to replace a column with a parameter in SQL?
I have a table which has 12 columns a1, a2,...,a12

I now want to replace that number by a parameter I chose before running the query. (I'm building a DB query in Excel 2007 with paramters)

So for example if the parameter I chose is 1 than the SQL query should be: SELECT a1 FROM table.
But if the parameter is 2 the statement should read: SELECT a1+a2 FROM table.
Paramter 3: SELECT a1+a2+a3 FROM table an so on.

Is that possible at all?

 

[markros]

It is possible by using dynamic SQL. However, constructing such complex query can be also done on the client.

PluralSight Learning Library

 

[gmmastros]

You can use a CASE/WHEN statement to do this for. The "trick" to this query is that when you add 0 and any number, the result is the same. So....

Select   Case When @Parameter >= 1 Then a1 Else 0 End
       + Case When @Parameter >= 2 Then a2 Else 0 End
       + Case When @Parameter >= 3 Then a3 Else 0 End
       + Case When @Parameter >= 4 Then a4 Else 0 End
       As YourColumnAlias
From   YourTableName

Basically, you check the value of the parameter and decide whether to use the value from the column or the value 0.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JonFer]

You could also construct a table like this (assuming only 3 fields):

Parm M1 M2 M3
1 1 0 0
2 1 1 0
3 1 1 1

Your select would join to this table "Where Parm = @Parameter" to get the appropriate row and calculate the value like this:

(a1*M1 + a2*M2 + a3*M3) as YTD

Similar concept to the gmmastros post but without CASE statements.

It's not very user-friendly for Excel but if you normalize your table by adding a MonthNum column, then the Where is just "MonthNum <= @Parameter".

John

 

[johnhugh]

Excellent ideas.
Thank you for those.