|
步骤一：创建测试表
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
--对应的执行结果如下： （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
