oracle实现某一列的值转换为列标题

步骤一：创建测试表

CREATE TABLE EMP
(
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
);

步骤二：插入测试数据

INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-7-1987','dd-mm-yyyy')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-7-87', 'dd-mm-yyyy')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
commit;

 

 

步骤三：实现列值转换为列标题（这里以JOB列进行转换）

declare
c1 sys_refcursor;
v_job emp.job%type;
v_sql varchar2(1024):='';
begin
  v_sql:='select deptno';
  open c1 for select distinct job from emp;
  loop
    fetch c1 into v_job;
    exit when c1%notfound;
    v_sql:=v_sql||',count(case job when '''||v_job||''' then job else null end) as '||v_job;
  end loop;
  close c1;
  v_sql:=v_sql||' from emp group by deptno';
  dbms_output.put_line(v_sql);--这条语句的输出，就是我们想要的sql语句
end;
/

 

--输出语句如下

select deptno,
count(case job when 'CLERK' then job else null end) as CLERK,
count(case job when 'SALESMAN' then job else null end) as SALESMAN,
count(case job when 'PRESIDENT' then job else null end) as PRESIDENT,
count(case job when 'MANAGER' then job else null end) as MANAGER,
count(case job when 'ANALYST' then job else null end) as ANALYST
from emp group by deptno

--对应的执行结果如下：