oracle行转列

oracle行转列(动态行转不定列)

思路：创建一个存储过程，用存储过程来动态的创建更新查询视图

1、视图定义如下
create or replace view emp_view as
select deptno,
       count(case when job='MANAGER' then empno end) MANAGER,
       count(case when job='SALESMAN' then empno end) SALESMAN,
       count(case when job='ANALYST' then empno end) ANALYST,
       count(case when job='PRESIDENT' then empno end) PRESIDENT,
       count(case when job='CLERK' then empno end) CLERK      
 from emp
 group by deptno
 order by deptno;

2、创建存储过程来创建视图
create or replace procedure auto_change_line is
  v_str_col varchar2(600);
  v_str_sql varchar2(1000);
begin
  for c in (select distinct job from emp) loop
    v_str_col := v_str_col || 'count(case when job=''' || c.job ||
                 ''' then empno end) ' || c.job || ',';
  end loop;
  v_str_col := rtrim(v_str_col, ',');
  v_str_sql := 'create or replace view emp_view as select deptno,' ||
               v_str_col || ' from emp group by deptno order by deptno';
  execute immediate v_str_sql;
end;

3、执行存储过程
begin
  auto_change_line;
end;

4、查看视图
select * from emp_view;

DEPTNO      CLERK   SALESMAN  PRESIDENT    MANAGER    ANALYST
------ ---------- ---------- ---------- ---------- ----------
    10          1          0          1          1          0
    20          2          0          0          1          2
    30          1          4          0          1          0

5、添加新的列
insert into emp (empno,ename,job,sal,deptno)
values(8000,'JDS','DBA',8000,40);
commit;

6、重新执行存储过程
begin
  auto_change_line;
end;

7、重新查看视图(增加了一列)
select * from emp_view;
DEPTNO      CLERK   SALESMAN        DBA  PRESIDENT    MANAGER    ANALYST
------ ---------- ---------- ---------- ---------- ---------- ----------
    10          1          0          0          1          1          0
    20          2          0          0          0          1          2
    30          1          4          0          0          1          0
    40          0          0          1          0          0          0

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17012874/viewspace-701750/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17012874/viewspace-701750/