17,082
社区成员
发帖
与我相关
我的任务
分享
select user_id, user_name,
count(decode(fun_id,1,1,0) 功能1,
count(decode(fun_id,2,1,0) 功能2,
count(decode(fun_id,3,1,0) 功能3,
count(decode(fun_id,4,1,0) 功能4,
count(decode(fun_id,5,1,0) 功能5,
count(decode(fun_id,6,1,0) 功能6,
..............
count(decode(fun_id,12,1,0) 功能12
from a
group by user_id, user_name
select user_id, user_name,
count(case when fun_id=1 then 1 end) f1,
count(case when fun_id=2 then 1 end) f2,
count(case when fun_id=3 then 1 end) f3,
...
count(case when fun_id=12 then 1 end) f12
from a
group by user_id, user_name
你的问题和这个类似
--测试数据
create table test1(id int,cdate varchar2(2),shop varchar2(100), y_com varchar2(100), total int);
insert into test1
select 1,'01','北京','运输公司1',4 from dual union all
select 2,'01','北京','运输公司2',5 from dual union all
select 3,'01','北京','运输公司3',6 from dual union all
select 4,'01','北京','运输公司1',7 from dual union all
select 5,'01','北京','运输公司1',8 from dual union all
select 6,'01','北京','运输公司1',9 from dual union all
select 7,'01','北京','运输公司1',10 from dual;
--行列转换
create or replace procedure getRstData( rst out sys_refcursor)
is
begin
declare
cursor cur is select y_com,sum(total) s
from test1
group by cdate,shop,y_com;
t1 varchar2(100);
t2 varchar2(100);
str varchar2(4000);
begin
str:='select cdate,shop';
open cur;
loop
fetch cur into t1,t2;
exit when cur%notfound;
str:=str||','''||t1||''',''(台数'||t2||')''';
end loop;
str:=str||' from test1 group by cdate,shop';
--dbms_output.put_line(str);
close cur;
open rst for str;
end;
end;
--输出结果
1 01 北京 运输公司1 (台数38) 运输公司2 (台数5) 运输公司3 (台数6)