本来有这么一个表
Date CountLog CountIndividual
02-Jun-06 5 1
03-Jun-06 11 2
.....
...
.
12-Dec-06 23 5
我想修改成这样的结构
Day JunCountLog JunCountInd JulCountLog JulCountInd SepCountLog......
1 5 1 23 3 15 ....
2 .............................................
3 ......................
..
31 ....
在建立新表结构以后，请问我如何把数据从第一个表转移到第二个表。请给出相关的SQL语句，谢谢！！！
------解决方案--------------------
先分列（每列中是自己月的取实际值不是的取０），然后求和
selelct date,sum(JunCountLog) as JunCountLog, sum(JunCountInd) as JunCountInd
sum(JulCountLog) as JulCountLog, sum(JulCountInd) as JulCountInd
...
from
(
select to_number(to_char(date, 'dd ')) as Day,
decode(to_char(date, 'mm '), '06 ',CountLog ,0) as JunCountLog ,
decode(to_char(date, 'mm '), '06 ',CountIndividual ,0) as JunCountInd ,
decode(to_char(date, 'mm '), '07 ',CountLog ,0) as JulCountLog ,
decode(to_char(date, 'mm '), '06 ',CountIndividual ,0) as JulCountLog ,
...
from table
)
group by date
------解决方案--------------------
打错了一个字段名，应该这样：
selelct Day,sum(JunCountLog) as JunCountLog, sum(JunCountInd) as JunCountInd
sum(JulCountLog) as JulCountLog, sum(JulCountInd) as JulCountInd
...
from
(
select to_number(to_char(date, 'dd ')) as Day,
decode(to_char(date, 'mm '), '06 ',CountLog ,0) as JunCountLog ,
decode(to_char(date, 'mm '), '06 ',CountIndividual ,0) as JunCountInd ,
decode(to_char(date, 'mm '), '07 ',CountLog ,0) as JulCountLog ,
decode(to_char(date, 'mm '), '06 ',CountIndividual ,0) as JulCountLog ,
...
from table
)
group by date
------解决方案--------------------
倒，上万个记录是小记录集，不上１０w一般都不算大；
decode的目的是先把每行仅有的几列分解为按月份区分的多列，符合的列取本月值，不符合本行信息的列取值为０
decode()过滤后的结果集：
Day JunCountLog JunCountInd JulCountLog JulCountInd SepCountLog......
６ 5 1 ０ ０ ０ ....
６ ０ ０ ２３ ３ ０ ．．．
7 ......................
.....
31 ....
求和是为了把本来应该在一行但是被分在了多行的值合并在一行上，如从１月１日到１２月１日都有记录，那么上一流程会生成１２行信息，但每行只有对应月份字段有值其他字段为０，分组求和后也就把１２个月所有１号行都合成了一行，而且每个月的值都是准确的，如：