how to pick a column based on a parameter

Question

Post reply

how to pick a column based on a parameter

isaac briseño

Grasshopper

Points: 19
More actions
May 23, 2006 at 6:40 pm

#168215

Hi, I have 12 columns, one for each month, the column has a float value I need to retrieve based on a parameter that my stored procedure will receive, along with other 5 columns that have descriptions, ids and other stuff
What is the more efficient way to structure the query? or do i have to repeat the query 12 times?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

You need to normalise your table.

_____________
Code for TallyGenerator

stax68 SSChampion Points: 11711 More actions · Answer 2

as a short-term fix (until you implement the above recommendation, perhaps with a table holding yearid, monthid, your float value and a FK back to the original table), you can use the simple case statement:

select
tab.id
,tab.description
,tab.stuff
,@month M
,case @month
when @month = 1 then tab.M1
when @month = 2 then tab.M2
...
when @month = 12 then tab.M12 M_total
from
whatevertable tab

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

stax68 SSChampion Points: 11711 More actions · Answer 3

sorry, should be:

select
tab.id
,tab.description
,tab.stuff
,@month M
,case @month
when 1 then tab.M1
when 2 then tab.M2
...
when 12 then tab.M12 M_total
from
whatevertable tab

I half-changed it from searched to simple

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

stax68 SSChampion Points: 11711 More actions · Answer 4

sorry again, should be:

select

tab.id

,tab.description

,tab.stuff

,@month M

,case @month

when 1 then tab.M1

when 2 then tab.M2

...

when 12 then tab.M12

end M_total

from

whatevertable tab

I promise to check my code properly in future...

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant