现有数据
A 2012/2/15 1
A 2012/2/16 2
A 2012/2/17 2.5
B 2012/2/15 1.2
B 2012/2/16 2.2
B 2012/2/17 2.6
C 2012/2/15 2.6
C 2012/2/16 1.8
C 2012/2/17 2.1
...ABC的个数是可变的,但是是可知的希望通过sql查询出来变成
A B C ...
2012/2/15 1 1.2 2.6
2012/2/16 2 2.2 1.8
2012/2/17 2.5 2.6 2.1
A 2012/2/15 1
A 2012/2/16 2
A 2012/2/17 2.5
B 2012/2/15 1.2
B 2012/2/16 2.2
B 2012/2/17 2.6
C 2012/2/15 2.6
C 2012/2/16 1.8
C 2012/2/17 2.1
...ABC的个数是可变的,但是是可知的希望通过sql查询出来变成
A B C ...
2012/2/15 1 1.2 2.6
2012/2/16 2 2.2 1.8
2012/2/17 2.5 2.6 2.1
create table tmp
( t_no varchar2(2),
t_date date,
t_num number(4,2)
)
查询
select t_date,
(select max(t_num) from tmp t2 where t2.t_date=t1.t_date and t2.t_no='A') A ,
(select max(t_num) from tmp t2 where t2.t_date=t1.t_date and t2.t_no='B') B ,
(select max(t_num) from tmp t2 where t2.t_date=t1.t_date and t2.t_no='C') C
from tmp t1
group by t_date
order by t_date
str_sql varchar2(2000);
str_sql_field varchar2(2000);
cursor cur_temp1 is
select distinct t_no from tmp order by t_no;
row_temp cur_temp1%rowtype;
begin
str_sql := 'select t_date';
for row_temp in cur_temp1
loop
str_sql_field := str_sql_field ||
',(select max(t_num) from tmp t2 where t2.t_date=t1.t_date and t2.t_no=''' ||
row_temp.t_no || ''')';
end loop;
str_sql := str_sql || str_sql_field || 'from tmp t1 group by t_date order by t_date';
dbms_output.put_line(str_sql);
execute immediate str_sql;exception
when others then
dbms_output.put_line('error');
end P_select;
给个存储过程
2012/2/15 1 1.2 2.6
2012/2/16 2 2.2 1.8
2012/2/17 2.5 2.6 2.1
WMSYS.WM_CONCAT(值) TIME FROM T
GROUP BY 日期
(select 日期,值 A from table where col='A') a,
(select 日期,值 B from table where col='B') b,
(select 日期,值 C from table where col='A') c
where a.日期=b.日期 and b.日期=c.日期
如果行数不一样多就外联