17,086
社区成员
发帖
与我相关
我的任务
分享
--创建测试表,添加数据
create table tt(no varchar(20 char), sex number);
insert into tt values('004',2);
insert into tt values('002',2);
insert into tt values('002',2);
insert into tt values('003',1);
insert into tt values('002',1);
commit;
--1.使用sql语句实现:
select no,
count(case sex when 1 then 1 else null end) c1,
count(case sex when 2 then 1 else null end) c2
from tt
group by no
order by no;
--(2)使用存储过程动态拼接
create or replace procedure row_to_line
is
str_sql varchar2(4000);
begin
str_sql := ' create or replace view v_row_to_line as select no ';
for x in (select distinct sex from tt) loop
str_sql := str_sql || ',count(decode(sex, '||x.sex||', 1 , null)) "'||x.sex||'"';
end loop;
str_sql := str_sql || ' from tt group by no order by no ';
execute immediate str_sql;
end;
/