select ID,TERM,score as china from cloud2012 where subject='china'
union all
select ID,TERM,score as math from cloud2012 where subject='math'
union all
select ID,TERM,score as english from cloud2012 where subject='english'
请问怎么行列合并,我想使用这种方法进行行列转换。
在存储过程中如何写转换的SQL。
union all
select ID,TERM,score as math from cloud2012 where subject='math'
union all
select ID,TERM,score as english from cloud2012 where subject='english'
请问怎么行列合并,我想使用这种方法进行行列转换。
在存储过程中如何写转换的SQL。
解决方案 »
- 在ORACLE里怎么让3个字段不能都相同
- 当给某表INERT预定记录时通过触发器同时再向该表追加几条记录?
- 高手请进,提一个小需求,帮忙实现一下
- oracle 11g的启动
- 从来没见过这么古怪的问题
- SQL如何动态生成列???
- 为何我控制台内--进放cd disk1 # ./runInstaller & 提示没有这个文件夹呢?大家帮我看看吧,谢谢了,高分相送!
- 急急急,关于ASP用Oracle Object for OLE访问Oracle数据库的问题
- 请教高手,怎样通过IE登录管理ORACLE,十分感谢
- 触发器中如何能够有效的捕获到表的某一字段发生改变吗?
- 问个弱的问题
- 求助:触发器报“ORA-04098: 触发器 'SCOTT.T_EMP1' 无效且未通过重新验证”的提示
001 2009 CHINA 77
001 2009 MATH 71
001 2009 ENGLISH 80id TERM CHINA ENGLISH MATH
001 2009 77 80 71
请问怎么用union all 进行转换?
SQL> with t as(
2 select '001' id,'2009' term,'china' subject,77 score from dual union all
3 select '001','2009','math',71 from dual union all
4 select '001','2009','english',80 from dual)
5 select id,term,
6 max(case subject when 'china' then score end) china,
7 max(case subject when 'math' then score end) math,
8 max(case subject when 'english' then score end) english
9 from t
10 group by id,term;ID TERM CHINA MATH ENGLISH
--- ---- ---------- ---------- ----------
001 2009 77 71 80
sum(decode (subject,'china',score,o)) china,
sum(decode (subject, 'math',score,0)) math,
sum(decode (subject,'english'score,0)) english
from table_name
group by id,term
max(case subject when 'china' then score end) china,
max(case subject when 'math' then score end) math,
max(case subject when 'english' then score end) english
from a
group by id,term;