数据库数据表现形式转换

Question

如下数据所示
  Year   WEEK  TYPE  PRICE
--------------------------
  2008    01    A     1
  2008    02    A     2
  2008    03    A     3
  2008    04    A     4
--------------------------
  2008    02    B     2
  2008    04    B     4
--------------------------
  2008    01    C     1
  2008    02    C     2将以上的数据转换为一下形式： 2008      A       01  1      02  2        03  3       04  4
-----------------------------------------------------------------
 2008      B                  02  2                    04  4
-----------------------------------------------------------------
 2008      C       01  1      02  2    请问该如何实现                     

Answer 1

select year, type, 
    decode(week1_price,0,null,'01') week1, decode(week1_price,0,null,week1_price) week1_price,
    decode(week1_price,0,null,'02') week2, decode(week1_price,0,null,week1_price) week2_price,
    decode(week1_price,0,null,'03') week3, decode(week1_price,0,null,week1_price) week3_price,
    decode(week1_price,0,null,'04') week4, decode(week1_price,0,null,week1_price) week4_price
from (
 select year, type, 
   sum(case when week='01' then price else 0 end) week1_price,
   sum(case when week='02' then price else 0 end) week2_price,
   sum(case when week='03' then price else 0 end) week3_price,
   sum(case when week='04' then price else 0 end) week4_price
  from t
  group by year, type
);

Answer 2

很简单的SQL：select 
  year,
  type,
  decode(week, '01','01')         week_1, 
  max(decode(week, '01',price))   price_1, 
  decode(week, '02','02')         week_2, 
  max(decode(week, '02',price))   price_2, 
  decode(week, '03','03')         week_3, 
  max(decode(week, '03',price))   price_3, 
  decode(week, '04','04')         week_4, 
  max(decode(week, '04',price))   price_4
from tablename 
group by year, type 
); 

Answer 3

楼主自己把SQL测试一下吧, 以上SQL中只会返回WEEK为01,02,03,04的部分. 如果你的WEEK还有很多,就得另行考虑了.ORACLE最新有PIVOT子句,专门用来做转置的.

Answer 4

而且TYPE实际上是一个联合主键