我根据这句话 select * from Forbug_HTMHkZje
得到的集合是
id deptcode time money
1 001014 2011-04-01 00:00:00:000 1000000
2 001014 2011-05-01 00:00:00:000 5000000
3 001013 2011-05-01 00:00:00:000 3000000
4 001013 2011-04-01 00:00:00:000 2000000
5 001015 2011-06-01 00:00:00:000 350000
6 001016 2011-04-01 00:00:00:000 25000000
我怎么写sql得到这样一个集合呢?
时间 001014 001013 001015 001016 --这一行标题,不用管,只用下面的集合
2011-04-01 1000000 2000000 0 25000000
2011-05-01 5000000 3000000 0 0
2011-06-01 0 0 350000 0
要得到这个集合,怎么写sql呢?高手帮忙看看啊。
得到的集合是
id deptcode time money
1 001014 2011-04-01 00:00:00:000 1000000
2 001014 2011-05-01 00:00:00:000 5000000
3 001013 2011-05-01 00:00:00:000 3000000
4 001013 2011-04-01 00:00:00:000 2000000
5 001015 2011-06-01 00:00:00:000 350000
6 001016 2011-04-01 00:00:00:000 25000000
我怎么写sql得到这样一个集合呢?
时间 001014 001013 001015 001016 --这一行标题,不用管,只用下面的集合
2011-04-01 1000000 2000000 0 25000000
2011-05-01 5000000 3000000 0 0
2011-06-01 0 0 350000 0
要得到这个集合,怎么写sql呢?高手帮忙看看啊。
decode()函数来实现。
cursor col_list is
with t as
(
select '001014' as deptcode, '2011-04-01' as time, 1000000 as money from dual
union all
select '001014' as deptcode, '2011-05-01' as time, 5000000 as money from dual
union all
select '001013' as deptcode, '2011-05-01' as time, 3000000 as money from dual
union all
select '001013' as deptcode, '2011-04-01' as time, 2000000 as money from dual
union all
select '001015' as deptcode, '2011-06-01' as time, 350000 as money from dual
union all
select '001016' as deptcode, '2011-04-01' as time, 2500000 as money from dual
)
select distinct deptcode from t order by deptcode;
str_sql varchar2(4000);
col_end varchar2(20);
begin
col_end := ',' || chr(10);
str_sql := 'select time' || col_end;
for col in col_list
loop
str_sql := str_sql || 'sum(case when deptcode = ''' || col.deptcode
|| ''' then money else 0 end) as "' || col.deptcode || '"' || col_end;
end loop;
str_sql := substr(str_sql, 1, length(str_sql) - length(col_end));
str_sql := str_sql || ' from t group by time order by time';
dbms_output.put_line(str_sql);
end;
打印出来的 str_sql 就是你最后需要的
我还没有找到一种 可以自己根据查询结果 来进行行列转换了
我后来自己是通过网页界面来实现的 没有用sql来实现
(
select '001014' as deptcode, '2011-04-01' as time, 1000000 as money from dual
union all
select '001014' as deptcode, '2011-05-01' as time, 5000000 as money from dual
union all
select '001013' as deptcode, '2011-05-01' as time, 3000000 as money from dual
我说的意思是,这一块我怎么写呢?我这边的 部门编号(动态的) 001014 001015 001016是根据这个表查出来的。