曾经也找过不少地方,可是没有很好的解决方法,这是我写的一个,不限制原行数的
declare
v_kcb varchar2(10);
v_sl number;
cursor c_kcb is select kcb from exam_1;
begin
execute immediate 'drop table exam_2';
execute immediate 'create table exam_2( id number)';
execute immediate 'insert into exam_2(id) values (1)';
open c_kcb;
loop
fetch c_kcb into v_kcb;
exit when c_kcb%notfound;
select sl into v_sl from exam_1 where kcb=v_kcb;
execute immediate 'ALTER TABLE EXAM_2 ADD('|| v_kcb||' number )';
execute immediate 'update exam_2 set '||v_kcb||'='||v_sl;
end loop;
close c_kcb;
commit;
end;
declare
v_kcb varchar2(10);
v_sl number;
cursor c_kcb is select kcb from exam_1;
begin
execute immediate 'drop table exam_2';
execute immediate 'create table exam_2( id number)';
execute immediate 'insert into exam_2(id) values (1)';
open c_kcb;
loop
fetch c_kcb into v_kcb;
exit when c_kcb%notfound;
select sl into v_sl from exam_1 where kcb=v_kcb;
execute immediate 'ALTER TABLE EXAM_2 ADD('|| v_kcb||' number )';
execute immediate 'update exam_2 set '||v_kcb||'='||v_sl;
end loop;
close c_kcb;
commit;
end;
名称 是否为空? 类型
----------------------------------------- -------- -----------------------
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)SQL*PLus> select job, deptno, count(*)
2 from emp
3 group by job, deptno;JOB DEPTNO COUNT(*)
--------- ---------- ----------
CLERK 10 2
CLERK 20 4
CLERK 30 2
ANALYST 20 4
MANAGER 10 2
MANAGER 20 2
MANAGER 30 2
SALESMAN 30 8
PRESIDENT 10 2已选择9行。SQL*PLus> select job,
2 max( decode( deptno, 10, cnt, null ) ) dept_10,
3 max( decode( deptno, 20, cnt, null ) ) dept_20,
4 max( decode( deptno, 30, cnt, null ) ) dept_30,
5 max( decode( deptno, 40, cnt, null ) ) dept_40
6 from ( select job, deptno, count(*) cnt
7 from emp
8 group by job, deptno )
9 group by job
10 /JOB DEPT_10 DEPT_20 DEPT_30 DEPT_40
--------- ---------- ---------- ---------- ----------
ANALYST 4
CLERK 2 4 2
MANAGER 2 2 2
PRESIDENT 2
SALESMAN 8-----------------------------------------------------------------------------------
各位,我有如下一个工资表,如:
姓名 工资项 工资
张三 基本工资 1000
张三 岗位工资 2000
张三 效益工资 200
李四 基本工资 1000
李四 效益工资 1000
.......我需要用一个SQL语句得到如下所示的结果:
姓名 基本工资 岗位工资 效益工资 .....
张三 1000 2000 200 .....
李四 1000 0 1000 .....
....
select 姓名, sum(decode(工资项,'基本工资',工资,0)) 基本工资,
sum(decode(工资项,'岗位工资',工资,0)) 岗位工资, ....
from yourtable
group by 姓名
姓名 工资项 工资
张三 基本工资 1000
张三 岗位工资 2000
张三 效益工资 200
李四 基本工资 1000
李四 效益工资 1000
.......我需要用一个SQL语句得到如下所示的结果:
姓名 基本工资 岗位工资 效益工资 .....
张三 1000 2000 200 .....
李四 1000 0 1000 .....
....
select 姓名, sum(decode(工资项,'基本工资',工资,0)) 基本工资,
sum(decode(工资项,'岗位工资',工资,0)) 岗位工资, ....
from yourtable
group by 姓名这个例子就很好啊,不过这属于行转列的,列转行这要看具体情况了
如果是列有一定的范围,而且是number类型的
比较方便