Turning Multiple Rows Into Single Row/Multiple Columns 3

[SWTpaul]

I have a Forecast table that has the the previous year, current year, and future year forecast figures for items. I have a query that simply returns the current month forecast and the future 11 months.

I need to be able to create a report that will list one row per item with the quantities in seperate columns. I have been scratching my head on how to achieve this. If you have any ideas please let me know!!!

Thanks!

Goal Layout
------------
Item | Nov | Dec | Jan | Feb | Mar | ... | Oct


Table Schema
------------
Item_ID
Date_ID
Quantity


Example Data
------------
01001 | 2006-11-01 | 9382
01001 | 2006-12-01 | 2384
01001 | 2007-01-01 | 5812
01001 | 2007-02-01 | 3124
.
.
01001 | 2007-10-01 | 1578

 

[SQLSister]

use case statements to filter each month by date they would be simliar to this for each month
sum(case when datefield between '2006-02-01' and '2006-03-01' then quantity else 0 end) As 'Jan',

Questions about posting. See faq183-874

 

[gmmastros]

Declare @CurrentMonth Int
Set @CurrentMonth = DateDiff(Month, 0, GetDate())

Select Item_Id, 
       Sum(Case When DateDiff(Month, 0, Date_Id) = @CurrentMonth
                Then Quantity
                Else 0
                End) As CurrentMonth,
       Sum(Case When DateDiff(Month, 0, Date_Id) = @CurrentMonth + 1
                Then Quantity
                Else 0
                End) As [CurrentMonth+1],
       Sum(Case When DateDiff(Month, 0, Date_Id) = @CurrentMonth + 2
                Then Quantity
                Else 0
                End) As [CurrentMonth+2]
From   @Temp
Group By Item_Id

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[hvass]

Could do it with a simple xtab

select SQL_BIG_RESULT
item_id,
sum(if(year(Date_id)=2006 and month(Date_id)=11,Quantity,0)) as nov,
sum(if(year(Date_id)=2006 and month(Date_id)=12,Quantity,0)) as dec,
sum(if(year(Date_id)=2007 and month(Date_id)=1,Quantity,0)) as jan,
sum(if(year(Date_id)=2007 and month(Date_id)=2,Quantity,0)) as feb,
etc..
from tablename
group by item_id

Only becomes a problem if you have a unknown number of columns - so I use a simple perl script that generates a xtab of any size but still generating code in the above style...

 

[hvass]

woops scrub my last post I realise I just strayed over from the mysql forum without realising it