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

前端之家收集整理的这篇文章主要介绍了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,
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',1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD',to_date('22-2-1981',1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981',2975,20);
INSERT INTO EMP VALUES
(7654,'MARTIN',to_date('28-9-1981',1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE',to_date('1-5-1981',2850,30);
INSERT INTO EMP VALUES
(7782,'CLARK',to_date('9-6-1981',2450,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-7-1987','dd-mm-yyyy')-85,3000,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',to_date('17-11-1981',5000,10);
INSERT INTO EMP VALUES
(7844,'TURNER',to_date('8-9-1981',1500,30);
INSERT INTO EMP VALUES
(7876,'ADAMS',7788,to_date('13-7-87','dd-mm-yyyy')-51,1100,20);
INSERT INTO EMP VALUES
(7900,'JAMES',to_date('3-12-1981',950,30);
INSERT INTO EMP VALUES
(7902,'FORD',20);
INSERT INTO EMP VALUES
(7934,'MILLER',7782,to_date('23-1-1982',1300,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

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

猜你在找的Oracle相关文章