表T1：
ITEMCODE ITEMDES YEAR MONTH PRICE
0101 钢笔 2007 1 10.00
0102 圆珠笔 2007 1 5.00
0101 钢笔 2007 2 11.00
0102 圆珠笔 2007 2 3.00
0101 钢笔 2007 3 9.00
0102 圆珠笔 2007 3 6.00
0101 钢笔 2007 4 15.00
0102 圆珠笔 2007 5 1.00
............
0101 钢笔 2007 11 6.00
0102 圆珠笔 2007 12 3.00
现在要从T1中实时生成以下结构，该怎么做？
ITEMCODE ITEMDES YEAR JANUARY FEBRURY MARCH APRIL MAY .... DECEMBER
0101 钢笔 2007 10 11 9 15 ...... 11
0102 圆珠笔 2007 5 3 6 ........... 3
------解决方案--------------------
select jan.ITEMCODE,jan.ITEMDES,jan.YEAR,jan.price JANUARY,feb.price FEBRURY.....dec.price DECEMBER
from
(select *
from t1
where
month= '1 ') jan,
(select *
from t1
where
month= '2 ') feb,
.
.
.
(select *
from t1
where
month= '12 ') dec,
where jan.ITEMCODE=feb.ITEMCODE,.....nov.ITEMCODE=dec.ITEMCODE
and jan.year=feb.year..............nov.year=dec.year
------解决方案--------------------
select
ITEMCODE,ITEMDES,YEAR,
MAX(DECODE(MONTH, 1,PRICE,0)) as JANUARY,,
MAX(DECODE(MONTH, 2,PRICE,0)) as FEBRURY ,
MAX(DECODE(MONTH, 3,PRICE,0)) as MARCH ,
MAX(DECODE(MONTH, 4,PRICE,0)) as APRIL ,
MAX(DECODE(MONTH, 5,PRICE,0)) as MAY ,
....
MAX(DECODE(MONTH,12,PRICE,0)) as DECEMBER
from
T1
group by
ITEMCODE,ITEMDES,YEAR