oracle不定列,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) &#