假如我在oracle数据库中存在如下的表student:
sNO sName sSex cid c
1 张三 男 001 84
2 李四 女 012 89
3 王五 男 004 85
请问如何使用sql语句将上面的表更改为:
sNO 1 2 3
sName 张三 李四 王五
sSex 男 女 男
cid 001 012 004
c 84 89 85
sNO sName sSex cid c
1 张三 男 001 84
2 李四 女 012 89
3 王五 男 004 85
请问如何使用sql语句将上面的表更改为:
sNO 1 2 3
sName 张三 李四 王五
sSex 男 女 男
cid 001 012 004
c 84 89 85
-------------------------------------------------------------------------------------
CREATE OR REPLACE procedure TRANSFER_RL AS
begin
declare
type col_type is table of varchar2(100) index by binary_integer;
cols col_type;
col_tmp col_type;
cnt number;
lp number:=0;
sqltxt varchar2(1000) := '';
cursor c_student is
select SNO,
SNAME,
SSEX,
CID,
CMARK
from student ;
r_student c_student%rowtype;
cursor c_cols is
select column_name
from user_tab_columns
where table_name = 'STUDENT';
r_cols c_cols%rowtype;
begin
-- drop
begin
execute immediate 'drop table student_transfer';
exception when others then
null;
end; -- create
begin
sqltxt := 'create table student_transfer (';
select count(0)
into cnt
from student;
for t in 1..cnt+1 loop
sqltxt := sqltxt || ' col_'|| t || ' varchar2(100),';
end loop;
sqltxt := sqltxt || 'constraint pk primary key(col_1) using index)';
execute immediate sqltxt;
exception when others then
null;
end; -- insert(head)
begin
for r_cols in c_cols loop
exit when c_cols%notfound;
lp := lp + 1;
col_tmp(lp) := r_cols.column_name;
sqltxt := 'insert into student_transfer(col_1) values (''' || col_tmp(lp) || ''')';
execute immediate sqltxt;
end loop;
exception when others then
null;
end; --insert(detail)
begin
for r_student in c_student loop
exit when c_student%notfound;
cols(1) := r_student.SNO;
cols(2) := r_student.SNAME;
cols(3) := r_student.SSEX;
cols(4) := r_student.CID;
cols(5) := r_student.CMARK;
for t in 1..lp loop
sqltxt := 'update student_transfer set col_' || (c_student%rowcount+1) || ' = ''' || cols(t) || '''';
sqltxt := sqltxt || ' where col_1 = ''' || col_tmp(t) || '''';
execute immediate sqltxt;
end loop;
end loop;
exception when others then
null;
end; -- last commit
commit;
end;
end;
/----------------------------------------------------------------------------------------------------
---------------------------------------
SNO SNAME SSEX CID CMARK
1 ZhangSan M 001 84
2 LiSi F 012 89
3 WangWu M 004 85
---------------------------------------运行后,表student_transfer的数据如下:
---------------------------------------
COL_1 COL_2 COL_3 COL_4
SNO 1 2 3
SNAME ZhangSan LiSi WangWu
SSEX M F M
CID 001 012 004
CMARK 84 89 85
---------------------------------------