如何将多行数据合并成一行多列(不用存储过程)
数据:
objid date value type
01 2010-01-01 100 1
01 2010-01-01 200 2
01 2010-01-01 300 3
合并后的效果应如下:
objid date value1 value2 value3
01 2010-01-01 100 200 300
数据:
objid date value type
01 2010-01-01 100 1
01 2010-01-01 200 2
01 2010-01-01 300 3
合并后的效果应如下:
objid date value1 value2 value3
01 2010-01-01 100 200 300
给个合计的吧:
select objid,date,sum(decode(type,1,value,0)) as value1, sum(decode(type,2,value,0)) as value2, sum(decode(type,3,value,0)) as value3 from tbl group by objid,date order by objid,date;
是行转列...
谢谢楼上的解答,其实我要的合并后的效果应该是按时间来分组,如下
数据: 对象ID 时间 值 类型
objid date value type
01 2010-01-01 100 1
01 2010-01-01 200 2
01 2010-01-01 300 3
01 2010-01-02 600 1
01 2010-01-02 800 2
合并后的效果应如下:
objid date value1 value2 value3
01 2010-01-01 100 200 300
01 2010-01-02 600 800
select '01','2010-01-01',200,'2' from dual union all
select '01','2010-01-01',300,'3' from dual union all
select '01','2010-01-02',600,'1' from dual union all
select '01','2010-01-02',800,'2' from dual)
select objid,
"date",
sum(decode(type,'1',value))type1,
sum(decode(type,'2',value))type2,
sum(decode(type,'3',value))type3
from tt
group by objid,"date"
order by 1,2OBJID date TYPE1 TYPE2 TYPE3
01 2010-01-01 100 200 300
01 2010-01-02 600 800
不是这个效果吗
按objid和日期分组。如果要按天分组,而时间字段为date型且精确到时分秒,则trunc("date")
select '01' objid,to_date('2010-01-01','yyyy-mm-dd') "date",100 value,1 type from dual
union all
select '01' objid,to_date('2010-01-01','yyyy-mm-dd') "date",200 value,2 type from dual
union all
select '01' objid,to_date('2010-01-01','yyyy-mm-dd') "date",300 value,3 type from dual
union all
select '01' objid,to_date('2010-01-02','yyyy-mm-dd') "date",600 value,1 type from dual
union all
select '01' objid,to_date('2010-01-02','yyyy-mm-dd') "date",800 value,2 type from dual
)
select objid,"date",sum(decode(type,1,value,0)) as value1, sum(decode(type,2,value,0)) as value2,
sum(decode(type,3,value,0)) as value3 from temp group by objid,"date" order by objid,"date";
from tableA
group by objid, date出来的结果是用逗号分隔的,处理下就好了
01 2010-01-01 100,200,300
01 2010-01-02 600,800
a1 a2 a3 a4 a5 a6
张 0 1 0 1 0
张 1 2 1 1 1
张 1 1 1 1 1
张 1 1 1 1 1
转化为
a1 a2 a3 a4 a5 a6
张 3 5 3 4 3