在oracle中求一sql语句 （急）

[快跑蜗牛哥]

在oracle中求一sql语句
已知一表 a 有如下数据

usedate            chinaoil   exciusoil   excichoil    wtoil
2009-08-22        1           2               3          4
2009-08-23        9          10               11         12
2009-08-25        13         14               15         16

现在要求得到如下数据

                    2009-08-22     2009-08-23   2009-08-25
chinaoil        1             9            13
exciusoil       2            10            14
excichoil       3            11            15
wtoil           4            12            16

[超叔csdn]

select cc,
max(case when usedate='2009-08-22' then dd end),
max(case when usedate='2009-08-23' then dd end),
max(case when usedate='2009-08-25' then dd end)
from (
select a.usedate,b.cc,
case when b.cc='chinaoil' then chinaoil
when b.cc='exciusoil' then exciusoil
when b.cc='excichoil' then excichoil
when b.cc='wtoil' then wtoil
end dd
from a,(select 'chinaoil' cc from dual
union all
select 'exciusoil' cc from dual
union all
select 'excichoil' cc from dual
union all
select 'wtoil' cc from dual ) b )
group by cc
;

[小灰狼W]

列转行，union all

select max(decode(usedate,'2009-08-22',chinaoil))"2009-08-22",
  max(decode(usedate,'2009-08-23',chinaoil))"2009-08-23",
  max(decode(usedate,'2009-08-25',chinaoil))"2009-08-25" from a union all
select max(decode(usedate,'2009-08-22',exciusoil)),
  max(decode(usedate,'2009-08-23',exciusoil)),
  max(decode(usedate,'2009-08-25',exciusoil)) from a union all
select max(decode(usedate,'2009-08-22',excichoil)),
  max(decode(usedate,'2009-08-23',excichoil)),
  max(decode(usedate,'2009-08-25',excichoil)) from a union all
select max(decode(usedate,'2009-08-22',wtoil)),
  max(decode(usedate,'2009-08-23',wtoil)),
  max(decode(usedate,'2009-08-25',wtoil)) from a

不过记录条数多的话效率太低
如果只是为了显示效果，建议通过外部工具来实现

[inthirties]

select
title,
sum(decode(udpate, '2009-08-22', cnt, 0)) "2009-08-22",
sum(decode(udpate, '2009-08-23', cnt, 0)) "2009-08-23",
sum(decode(udpate, '2009-08-25', cnt, 0)) "2009-08-25"
from
(select update, 'chinaoil title, chinaoil cnt from t1
union
select update, 'exciusoil' title, exciusoil cnt from t1
union
select update, 'excichoil' title, excichoil cnt from t1
union
select update, 'wtoil' title, wtoil cnt from t1) group by title

[快跑蜗牛哥]

有没有更好的方法啊，
有时裱中的日期是变动的啊！

[小灰狼W]

[Quote=引用 4 楼 qw0907 的回复:]
有没有更好的方法啊，
有时裱中的日期是变动的啊！
[/Quote]字段名都变动的话，一条sql语句肯定是写不了
可以写个过程，拼出你需要的语句，再执行

[快跑蜗牛哥]

[Quote=引用 5 楼 wildwave 的回复:]
引用 4 楼 qw0907 的回复:
有没有更好的方法啊，
有时裱中的日期是变动的啊！
字段名都变动的话，一条sql语句肯定是写不了
可以写个过程，拼出你需要的语句，再执行
[/Quote]
哦，TKS！！

[快跑蜗牛哥]

如果不限制sql语句条数，该怎么实现啊！

[小灰狼W]

declare
sqlstr varchar2(2000):='select title';
begin
for cur1 in (select * from a)
loop
  sqlstr:=sqlstr||'
    ,max(decode(usedate,'''||cur1.usedate||''',cnt))"'||cur1.usedate||'"';
end loop;
sqlstr:=sqlstr||'
  from (select usedate,''chinaoil'' title,chinaoil cnt from a union all
    select usedate,''exciusoil'',exciusoil  from a union all
    select usedate,''excichoil'',excichoil from a union all
    select usedate,''wtoil'',wtoil  from a)
  group by title';
dbms_output.put_line(sqlstr);
end;

[cuisea]

declare
 strSql varchar2(2000);
 cursor datelist is select usedate from a;
 strDate varchar2(20);
begin
 strSql:='select num';
 open datelist;
 loop
  fetch datelist into strDate;
  exit when datelist%notfound;
  strSql:=strSql||',sum(decode(usedate, '''||strDate||''', cnt,0))  "'||strDate||'"';
 end loop;
 close datelist;
 strSql:=strSql||' from (
select usedate,1 num,chinaoil cnt from a
union select usedate,2 num,exciusoil cnt from a
union select usedate,3 num,excichoil cnt from a
union select usedate,4 num,wtoil cnt from a)
group by num order by num asc';
dbms_output.put_line(strSql);
end;

加入一个num用来排序，还可以防止表里没有记录时组合的sql出错