展开全部
基本思路:对每班学生排序,根据e69da5e6ba9062616964757a686964616f31333332633537序号构造列名,拼接动态sql--测试数据
create table
("学生" varchar2(10)
,"学号" varchar2(10)
,"班级" varchar2(10)
);
insert into "表A"
select '张三','100','一班' from dual union all
select '李四','101','二班' from dual union all
select '王五','102','一班' from dual union all
select '赵六','103','三班' from dual union all
select '李二','104','二班' from dual
--动态拼接Pivot
declare
sqlstr varchar2(8000):='';
begin
--构造类似于 '学号1','学号2',... 的字符串
for x in (
select distinct row_number() over (partition by "班级" order by "学号") seq
from "表A" order by seq ) loop
sqlstr := sqlstr || ',''' || '学号' ||to_char(x.seq)||'''';
end loop;
sqlstr:=substr(sqlstr,2,length(sqlstr)-1);
--将前面构造的字符串放入Pivot语句中
sqlstr:='
select * from (
select "学号","班级", ''学号''|| to_char(
row_number() over (partition by "班级" order by "学号")) seq
from "表A") t
pivot(
max("学号")
for seq in ('||sqlstr||')
)';
--dbms_output.put_line(sqlstr);
--将查询结果放入临时视图中
sqlstr := 'CREATE OR REPLACE VIEW tmp_result AS '|| sqlstr;
--dbms_output.put_line(sqlstr);
execute immediate sqlstr;
end;
--查看结果
select * from tmp_result;
结果如下: