我有一个数据表,因为在程序设定初期,用户说明白他的所有要求,导致程序已经完全编制完成,他又来说要一个表,可是这个表就是整个得把以前的表竖转横!下面把表说明一下,select * from emp_ls where rownum<=10NAME DH GZDH HPDH AMOUNT YEARS
---------- ---------- ----- ----- -------------------- ----------
王枫鑫 7M B5.22 3无 2010
王枫鑫 7M B5.23 2无 2010
王枫鑫 7M B5.24 2无 2010
王枫鑫 7M B5.25 4无 2010
王枫鑫 7M B5.26 4无 2010
王枫鑫 7M B5.3 18无 2010
王枫鑫 7M B5.4 18无 2010
王枫鑫 7M B5.6 8无 2010
王枫鑫 7M B5.7 8无 2010
李树海 7M B4.1 12无 2010用户要求,把这个表中的hpdh这个转成横的,这个类别有81个大项,就是要一个是
序号 姓名 B1_1 B1_2…… B5_27
1 王枫鑫 null 3无 null
2 李树海 1 null null如果hpdh里有的,取值amount,如果没有,就是null。我在网上找了一个,可是不知道为什么就是限制在10条记录以内,可以执行,否则就出错!请各位高手指点!
select name,sum(case hpdh when 'B1.1' then amount else null end) B1_1,
sum(case hpdh when 'B1.2' then amount else null end ) B1_2,
sum(case hpdh when 'B1.3' then amount else null end ) B1_3,
sum(case hpdh when 'B1.4' then amount else null end ) B1_4,
sum(case hpdh when 'B1.5' then amount else null end ) B1_5,
sum(case hpdh when 'B1.6' then amount else null end ) B1_6,
sum(case hpdh when 'B1.7' then amount else null end ) B1_7,
sum(case hpdh when 'B1.8' then amount else null end ) B1_8,
sum(case hpdh when 'B1.9' then amount else null end ) B1_9,
sum(case hpdh when 'B1.10' then amount else null end ) B1_10,
sum(case hpdh when 'B1.11' then amount else null end ) B1_11,
sum(case hpdh when 'B1.12' then amount else null end ) B1_12,
sum(case hpdh when 'B1.13' then amount else null end ) B1_13,
sum(case hpdh when 'B1.14' then amount else null end ) B1_14,
sum(case hpdh when 'B1.15' then amount else null end ) B1_15,
sum(case hpdh when 'B1.16' then amount else null end ) B1_16,
sum(case hpdh when 'B1.17' then amount else null end ) B1_17,
sum(case hpdh when 'B1.18' then amount else null end ) B1_18,
sum(case hpdh when 'B1.19' then amount else null end ) B1_19,
sum(case hpdh when 'B1.20' then amount else null end ) B1_20,
sum(case hpdh when 'B1.21' then amount else null end ) B1_21,
sum(case hpdh when 'B1.22' then amount else null end ) B1_22,
sum(case hpdh when 'B1.23' then amount else null end ) B1_23,
sum(case hpdh when 'B1.24' then amount else null end ) B1_24,
sum(case hpdh when 'B1.25' then amount else null end ) B1_25,
sum(case hpdh when 'B1.26' then amount else null end ) B1_26,
sum(case hpdh when 'B1.27' then amount else null end ) B1_27,
sum(case hpdh when 'B1.28' then amount else null end ) B1_28,
sum(case hpdh when 'B2.1' then amount else null end) B2_1,
sum(case hpdh when 'B2.2' then amount else null end ) B2_2,
sum(case hpdh when 'B2.3' then amount else null end ) B2_3,
sum(case hpdh when 'B2.4' then amount else null end ) B2_4,
sum(case hpdh when 'B2.5' then amount else null end ) B2_5,
sum(case hpdh when 'B2.6' then amount else null end ) B2_6,
sum(case hpdh when 'B2.7' then amount else null end ) B2_7,
sum(case hpdh when 'B2.8' then amount else null end ) B2_8,
sum(case hpdh when 'B2.9' then amount else null end ) B2_9,
sum(case hpdh when 'B2.10' then amount else null end ) B2_10,
sum(case hpdh when 'B2.11' then amount else null end ) B2_11,
sum(case hpdh when 'B2.12' then amount else null end ) B2_12,
sum(case hpdh when 'B2.13' then amount else null end ) B2_13,
sum(case hpdh when 'B2.14' then amount else null end ) B2_14,
sum(case hpdh when 'B2.15' then amount else null end ) B2_15,
sum(case hpdh when 'B2.16' then amount else null end ) B2_16,
sum(case hpdh when 'B3.1' then amount else null end) B3_1,
sum(case hpdh when 'B3.2' then amount else null end ) B3_2,
sum(case hpdh when 'B3.3' then amount else null end ) B3_3,
sum(case hpdh when 'B3.4' then amount else null end ) B3_4,
sum(case hpdh when 'B3.5' then amount else null end ) B3_5,
sum(case hpdh when 'B3.6' then amount else null end ) B3_6,
sum(case hpdh when 'B4.1' then amount else null end) B4_1,
sum(case hpdh when 'B4.2' then amount else null end ) B4_2,
sum(case hpdh when 'B4.3' then amount else null end ) B4_3,
sum(case hpdh when 'B4.4' then amount else null end ) B4_4,
sum(case hpdh when 'B4.5' then amount else null end ) B4_5,
sum(case hpdh when 'B5.1' then amount else null end) B5_1,
sum(case hpdh when 'B5.2' then amount else null end ) B5_2,
sum(case hpdh when 'B5.3' then amount else null end ) B5_3,
sum(case hpdh when 'B5.4' then amount else null end ) B5_4,
sum(case hpdh when 'B5.5' then amount else null end ) B5_5,
sum(case hpdh when 'B5.6' then amount else null end ) B5_6,
sum(case hpdh when 'B5.7' then amount else null end ) B5_7,
sum(case hpdh when 'B5.8' then amount else null end ) B5_8,
sum(case hpdh when 'B5.9' then amount else null end ) B5_9,
sum(case hpdh when 'B5.10' then amount else null end ) B5_10,
sum(case hpdh when 'B5.11' then amount else null end ) B5_11,
sum(case hpdh when 'B5.12' then amount else null end ) B5_12,
sum(case hpdh when 'B5.13' then amount else null end ) B5_13,
sum(case hpdh when 'B5.14' then amount else null end ) B5_14,
sum(case hpdh when 'B5.15' then amount else null end ) B5_15,
sum(case hpdh when 'B5.16' then amount else null end ) B5_16,
sum(case hpdh when 'B5.17' then amount else null end ) B5_17,
sum(case hpdh when 'B5.18' then amount else null end ) B5_18,
sum(case hpdh when 'B5.19' then amount else null end ) B5_19,
sum(case hpdh when 'B5.20' then amount else null end ) B5_20,
sum(case hpdh when 'B5.21' then amount else null end ) B5_21,
sum(case hpdh when 'B5.22' then amount else null end ) B5_22,
sum(case hpdh when 'B5.23' then amount else null end ) B5_23,
sum(case hpdh when 'B5.24' then amount else null end ) B5_24,
sum(case hpdh when 'B5.25' then amount else null end ) B5_25,
sum(case hpdh when 'B5.26' then amount else null end ) B5_26,
sum(case hpdh when 'B5.27' then amount else null end ) B5_27
from emp_ls group by name
---------- ---------- ----- ----- -------------------- ----------
王枫鑫 7M B5.22 3无 2010
王枫鑫 7M B5.23 2无 2010
王枫鑫 7M B5.24 2无 2010
王枫鑫 7M B5.25 4无 2010
王枫鑫 7M B5.26 4无 2010
王枫鑫 7M B5.3 18无 2010
王枫鑫 7M B5.4 18无 2010
王枫鑫 7M B5.6 8无 2010
王枫鑫 7M B5.7 8无 2010
李树海 7M B4.1 12无 2010用户要求,把这个表中的hpdh这个转成横的,这个类别有81个大项,就是要一个是
序号 姓名 B1_1 B1_2…… B5_27
1 王枫鑫 null 3无 null
2 李树海 1 null null如果hpdh里有的,取值amount,如果没有,就是null。我在网上找了一个,可是不知道为什么就是限制在10条记录以内,可以执行,否则就出错!请各位高手指点!
select name,sum(case hpdh when 'B1.1' then amount else null end) B1_1,
sum(case hpdh when 'B1.2' then amount else null end ) B1_2,
sum(case hpdh when 'B1.3' then amount else null end ) B1_3,
sum(case hpdh when 'B1.4' then amount else null end ) B1_4,
sum(case hpdh when 'B1.5' then amount else null end ) B1_5,
sum(case hpdh when 'B1.6' then amount else null end ) B1_6,
sum(case hpdh when 'B1.7' then amount else null end ) B1_7,
sum(case hpdh when 'B1.8' then amount else null end ) B1_8,
sum(case hpdh when 'B1.9' then amount else null end ) B1_9,
sum(case hpdh when 'B1.10' then amount else null end ) B1_10,
sum(case hpdh when 'B1.11' then amount else null end ) B1_11,
sum(case hpdh when 'B1.12' then amount else null end ) B1_12,
sum(case hpdh when 'B1.13' then amount else null end ) B1_13,
sum(case hpdh when 'B1.14' then amount else null end ) B1_14,
sum(case hpdh when 'B1.15' then amount else null end ) B1_15,
sum(case hpdh when 'B1.16' then amount else null end ) B1_16,
sum(case hpdh when 'B1.17' then amount else null end ) B1_17,
sum(case hpdh when 'B1.18' then amount else null end ) B1_18,
sum(case hpdh when 'B1.19' then amount else null end ) B1_19,
sum(case hpdh when 'B1.20' then amount else null end ) B1_20,
sum(case hpdh when 'B1.21' then amount else null end ) B1_21,
sum(case hpdh when 'B1.22' then amount else null end ) B1_22,
sum(case hpdh when 'B1.23' then amount else null end ) B1_23,
sum(case hpdh when 'B1.24' then amount else null end ) B1_24,
sum(case hpdh when 'B1.25' then amount else null end ) B1_25,
sum(case hpdh when 'B1.26' then amount else null end ) B1_26,
sum(case hpdh when 'B1.27' then amount else null end ) B1_27,
sum(case hpdh when 'B1.28' then amount else null end ) B1_28,
sum(case hpdh when 'B2.1' then amount else null end) B2_1,
sum(case hpdh when 'B2.2' then amount else null end ) B2_2,
sum(case hpdh when 'B2.3' then amount else null end ) B2_3,
sum(case hpdh when 'B2.4' then amount else null end ) B2_4,
sum(case hpdh when 'B2.5' then amount else null end ) B2_5,
sum(case hpdh when 'B2.6' then amount else null end ) B2_6,
sum(case hpdh when 'B2.7' then amount else null end ) B2_7,
sum(case hpdh when 'B2.8' then amount else null end ) B2_8,
sum(case hpdh when 'B2.9' then amount else null end ) B2_9,
sum(case hpdh when 'B2.10' then amount else null end ) B2_10,
sum(case hpdh when 'B2.11' then amount else null end ) B2_11,
sum(case hpdh when 'B2.12' then amount else null end ) B2_12,
sum(case hpdh when 'B2.13' then amount else null end ) B2_13,
sum(case hpdh when 'B2.14' then amount else null end ) B2_14,
sum(case hpdh when 'B2.15' then amount else null end ) B2_15,
sum(case hpdh when 'B2.16' then amount else null end ) B2_16,
sum(case hpdh when 'B3.1' then amount else null end) B3_1,
sum(case hpdh when 'B3.2' then amount else null end ) B3_2,
sum(case hpdh when 'B3.3' then amount else null end ) B3_3,
sum(case hpdh when 'B3.4' then amount else null end ) B3_4,
sum(case hpdh when 'B3.5' then amount else null end ) B3_5,
sum(case hpdh when 'B3.6' then amount else null end ) B3_6,
sum(case hpdh when 'B4.1' then amount else null end) B4_1,
sum(case hpdh when 'B4.2' then amount else null end ) B4_2,
sum(case hpdh when 'B4.3' then amount else null end ) B4_3,
sum(case hpdh when 'B4.4' then amount else null end ) B4_4,
sum(case hpdh when 'B4.5' then amount else null end ) B4_5,
sum(case hpdh when 'B5.1' then amount else null end) B5_1,
sum(case hpdh when 'B5.2' then amount else null end ) B5_2,
sum(case hpdh when 'B5.3' then amount else null end ) B5_3,
sum(case hpdh when 'B5.4' then amount else null end ) B5_4,
sum(case hpdh when 'B5.5' then amount else null end ) B5_5,
sum(case hpdh when 'B5.6' then amount else null end ) B5_6,
sum(case hpdh when 'B5.7' then amount else null end ) B5_7,
sum(case hpdh when 'B5.8' then amount else null end ) B5_8,
sum(case hpdh when 'B5.9' then amount else null end ) B5_9,
sum(case hpdh when 'B5.10' then amount else null end ) B5_10,
sum(case hpdh when 'B5.11' then amount else null end ) B5_11,
sum(case hpdh when 'B5.12' then amount else null end ) B5_12,
sum(case hpdh when 'B5.13' then amount else null end ) B5_13,
sum(case hpdh when 'B5.14' then amount else null end ) B5_14,
sum(case hpdh when 'B5.15' then amount else null end ) B5_15,
sum(case hpdh when 'B5.16' then amount else null end ) B5_16,
sum(case hpdh when 'B5.17' then amount else null end ) B5_17,
sum(case hpdh when 'B5.18' then amount else null end ) B5_18,
sum(case hpdh when 'B5.19' then amount else null end ) B5_19,
sum(case hpdh when 'B5.20' then amount else null end ) B5_20,
sum(case hpdh when 'B5.21' then amount else null end ) B5_21,
sum(case hpdh when 'B5.22' then amount else null end ) B5_22,
sum(case hpdh when 'B5.23' then amount else null end ) B5_23,
sum(case hpdh when 'B5.24' then amount else null end ) B5_24,
sum(case hpdh when 'B5.25' then amount else null end ) B5_25,
sum(case hpdh when 'B5.26' then amount else null end ) B5_26,
sum(case hpdh when 'B5.27' then amount else null end ) B5_27
from emp_ls group by name
解决方案 »
- 求助SQL语句汇总求合问题
- 左连接改成+的写法,出现ORA-01417: a table may be outer joined to at most one other table错误
- Oracle 10G windows 2003 system 系统从新安装过了请问怎么恢复之前的数据啊??
- 多客户端同时访问数据库何时加锁解锁
- SQL语句总是异常,太杯具了
- 求高手帮忙
- ORacel行列转换问题
- 如何采集oracle数据库的性能、告警参数?
- NUMBER的最大长度是多少?
- 请教一个Oracle9i配置完Oracle Management Server问题~
- 请教:如何恢复*.dmp备份文件中的某个表中部份的数据??
- 关与fromuser的问题
select name,
sum(decode(hpdh ,'B1.1',null)) B1_1,
........
sum(decode(hpdh ,'B5.27' ,null)) B5_27
from emp_ls
group by name
select name,
sum(decode(hpdh ,'B1.1',AMOUNT,null)) B1_1,
........
sum(decode(hpdh ,'B5.27' ,AMOUNT,null)) B5_27
from emp_ls
group by name
数据量超过10条会报错?
关注一下
NAME DH GZDH HPDH AMOUNT YEARS
---------- ---------- ----- ----- -------------------- ----------
王枫鑫 7M B5.22 3 无 2010
王枫鑫 7M B5.23 2 无 2010
王枫鑫 7M B5.24 2 无 2010
王枫鑫 7M B5.25 4 无 2010AMOUNT里面存的是无,怎么合计。
sum(case hpdh when 'B5.22' then amount else null end ) B5_22,
*
ERROR 位于第 77 行:
ORA-01722: invalid number
你给我的语句,我试了下,出以下报错:
sum(decode(hpdh ,'B1.1',AMOUNT,null)) B1_1
*
ERROR 位于第 2 行:
ORA-01722: invalid number
amount里存放的是一个阿拉伯数字和一个无字,因为有的东西没有尺寸只有数量!所以产生这个字段!
那怎么能相加的
没有出错是因为进行隐式转换,出错是因为涉及到的记录转换过程中出错,因为该字符串无法转换成数字