怎么把这张表转换成下面的形式(一条sql):
Year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
Year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
Year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
Year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
解决方案 »
- 求一本数据库的工具书
- 速救啊,sql死慢啊,模糊查询的问题
- Oracle与SQLServer数据同步问题
- oracle中查看一个包中定义了哪些过程和函数的方法
- 不固定行转列
- RMAN的配置参数如何修改问题???
- 如何 讲 oracle 中的某个表中数据 导出 成为csv文件
- 请大虾受累看看这个.pc文件!?
- 请问存储过程一个输入型的字符串(VARCHAR2)的参数最长是多少?
- 又有问题了:访问CLOB字段出现错误:java.sql.SQLException: 用尽的 Resultset
- 用一条sql语句查询出“每门”课程都大于80分的学生姓名
- 在oracle里面调用java,参数是不是有长度限制?
不满足要求,这样的sql 分不出来 m1 ,m2 ,m3 ,m4 列
SELECT year,
sum(DECODE(month,1,amount,0)) as m1,
sum(DECODE(month,2,amount,0)) as m2,
......
sum(DECODE(month,11,amount,0)) as m11,
sum(DECODE(month,12,amount,0)) as m12
from tab
group by year
max(decode(month,2,amount,null)) m2,
max(decode(month,3,amount,null)) m3,
max(decode(month,4,amount,null)) m4,
max(decode(month,5,amount,null)) m5,
max(decode(month,6,amount,null)) m6,
max(decode(month,7,amount,null)) m7,
max(decode(month,8,amount,null)) m8,
max(decode(month,9,amount,null)) m9,
max(decode(month,10,amount,null)) m10,
max(decode(month,11,amount,null)) m11,
max(decode(month,12,amount,null)) m12
from t group by year
不好意思,看错了
就只有1.2.3.4
select Year,sum(case when month=1 then amount end) m1,
sum(case when month=2 then amount end) m2,
sum(case when month=3 then amount end) m3,
sum(case when month=4 then amount end) m4
from tb
group by Year
sum(decode(month, 1, amount, 0)) m1,
sum(decode(month, 2, amount, 0)) m2,
sum(decode(month, 3, amount, 0)) m3,
sum(decode(month, 4, amount, 0)) m4
from table_name
group by year
sum(decode(month,1,amout,0))m1,
sum(decode(month,2,amout,0))m2,
sum(decode(month,3,amout,0))m3,
sum(decode(month,4,amout,0))m4
from ymd group by year;