Simpilify query for SQL table with 12 columns for month

Question

I have a table in production with 12 columns for each month. I need to create a SP where I pass in a productID, Customer and Month parameter and retrieve the sum of that month.

currently my logic is

if month = 1 then
  select sum(JAN) from table where productID = @id and customer = @cust
if month = 2 then
   select SUM(FEB) from table where productID = @id and customer = @cust

....

The query is a bit more involved but this is the core of it. Is there any way around these "IF" statements?

Edit - This is an SQL Server 2000 database but will be migrated to SQL Server 2005, the PIVOT and UNPIVOT will come in handy when I move to SQL Server 2005.

Answer 1

Have a look at UNPIVOT - you would unpivot to make the columns into rows and then use a WHERE clause to get only the rows for the month you are interested in.

Inside:

SELECT *
FROM tbl UNPIVOT FOR Month IN ([JAN], [FEB], etc...)

Then join to a table to translate month to number: JAN 1 FEB 2 etd.

Then WHERE MonthNum = @param

Answer 2

First thing that comes to mind - you could use dynamic sql to generate the query and and execute with a simple EXEC.

This sounds to me like a no hassle solution and for a such a simple query I don't see a reason not to go with the easiest option.

Answer 3

I think the difficulty in making the query stems from the fact that your database is not normalized correctly.

You appear to have a table with columns:

productId
customer
JAN
FEB
...

Either that or further up in the stored procedure you have created a temporary table that looks like that. This isn't the best way to do it in SQL. You should aim to have something like this as your table structure:

productId
customer
date
amount

Then you can write your query using a simple WHERE clause:

SELECT SUM(amount)
FROM table
WHERE MONTH(date) = month AND productID = @id and customer = @cust

You might also want to check the year in your query otherwise you could be summing data from January 2009 and January 2010. Or... maybe you have a table for each year too? (I hope not!)

---

Note: Of course, it may not actually be feasible for you to change the table structure at this point. I still felt that this is worth mentioning in case you were not aware of this issue. At least knowing about this may prevent the same mistake being made again in the future.

Answer 4

You could look at the UNPIVOT function in SQL Server - UnPivot

Answer 5

select sum(quantity) from table unpivot (quantity for month in (jan,feb,mar/*etc*/)) as t where where productID = @id and customer = @cust and month='jan'

Answer 6

How about:

declare @month int
set @month = 2
select sum(case @month
                when 1 then JAN
                when 2 then FEB
                when 3 then MAR
                when 4 then APR
                when 5 then MAY
                when 6 then JUN
                when 7 then JUL
                when 8 then AUG
                when 9 then SEP
                when 10 then OCT
                when 11 then NOV
                when 12 then DEC
                else 0
            end) 
from table 
where productID = @id 
and customer = @cust

Answer 7

select sum(case when @month = 1 then Jan
        when @month = 2 then Feb
        when @month = 3 then Mar
        when @month = 4 then Apr
        when @month = 5 then May
        when @month = 6 then Jun
        when @month = 7 then Jul
        when @month = 8 then Aug
        when @month = 9 then Sep
        when @month = 10 then Oct
        when @month = 11 then Nov
        when @month = 12 then [Dec] end) 
        from mytable
        where productID = @id and customer = @cust 

Assuming you declare and inialize the variables, this should work. It's more complicated of course if you wan to do multiple months, that's one reason why the design is not the best for querying.