我想问清楚,你那个结果是不是只有两列?如果是这样的话,用这个吧 select t.*, substr(( select max(sys_connect_by_path(ctext,' ')) result from (select cid,ctext,rn,lead(rn) over(partition by cid order by rn) rn1 from (select cid,ctext,row_number() over(order by cid,ctext desc) rn from testtable) ) start with cid = t.cid and rn1 is null connect by rn1 = prior rn ),2) value from (select distinct cid from testtable) t;
举个例子: FormID FieldName FieldDate 1 编号 2 1 发起人 张三 1 发起时间 2007-1-12 1 内容 你好 数据表B:列为FormID,CreateDate,CreateName现在想要一个视图或存储过程将AB表根据FormID拉成横向显示,如 FormID Code Name Date Content 1 2 张三 2007-1-12 你好 2 3 里司 2007-1-13 我好 *************************************** select FormID, sum(decode(FieldName, 编号, FieldDate, null)) Code, sum(decode(FieldName, 发起人, FieldDate, null)) Name, sum(decode(FieldName, 发起时间, FieldDate, null)) Date, sum(decode(FieldName, 内容, FieldDate, null)) Content from A group by FormID;多列的话,按照格式自行添加即可
如果不固定,自己写个function吧
如果不固定,自己写个function吧===> How ?
想破了头也没有想出更简洁的办法,我用了一个非常笨的方法实现的,希望有点帮助对你,呵呵 我假定现在你有两个字段,第一个字段的类型为char(2),第二个为varchar2(10) declare type ref_cur is ref cursor; type my_array is table of varchar(31) index by binary_integer; fieldname my_array; fieldindex integer; my_cur ref_cur; my_cur1 ref_cur; SqlStr varchar2(1000); temp my_source.col2%type; temp1 my_source.col2%type; begin SqlStr := 'drop table my_dest'; execute immediate SqlStr; SqlStr := 'Create table my_dest(col1 char(2)'; fieldindex := 1; open my_cur for select distinct(col2) from my_source order by col2; loop fetch my_cur into temp; exit when my_cur%notfound; SqlStr := SqlStr||','||temp||' varchar2(10)'; fieldname(fieldindex) := temp; fieldindex := fieldindex + 1; end loop; SqlStr := SqlStr ||')'; close my_cur; execute immediate SqlStr; open my_cur for select distinct(col1) from my_source order by col1; loop fetch my_cur into temp; exit when my_cur%notfound; SqlStr := 'insert into my_dest(col1) values('||''''||temp||''''||')'; execute immediate SqlStr; dbms_output.put_line(SqlStr); fieldindex := 1; open my_cur1 for select col2 from my_source where col1 = temp order by col2; loop fetch my_cur1 into temp1; exit when my_cur1%notfound; SqlStr := 'update my_dest set '||fieldname(fieldindex)||'='||''''||temp1||''''||'where col1 = '||''''||temp||''''; dbms_output.put_line(SqlStr); execute immediate SqlStr; fieldindex := fieldindex + 1; end loop; close my_cur1; end loop; close my_cur; COMMIT; end;
select t.*,
substr((
select max(sys_connect_by_path(ctext,' ')) result from
(select cid,ctext,rn,lead(rn) over(partition by cid order by rn) rn1
from (select cid,ctext,row_number() over(order by cid,ctext desc) rn from testtable)
)
start with cid = t.cid and rn1 is null connect by rn1 = prior rn
),2) value
from (select distinct cid from testtable) t;
FormID FieldName FieldDate
1 编号 2
1 发起人 张三
1 发起时间 2007-1-12
1 内容 你好
数据表B:列为FormID,CreateDate,CreateName现在想要一个视图或存储过程将AB表根据FormID拉成横向显示,如
FormID Code Name Date Content
1 2 张三 2007-1-12 你好
2 3 里司 2007-1-13 我好
***************************************
select FormID, sum(decode(FieldName, 编号, FieldDate, null)) Code,
sum(decode(FieldName, 发起人, FieldDate, null)) Name,
sum(decode(FieldName, 发起时间, FieldDate, null)) Date,
sum(decode(FieldName, 内容, FieldDate, null)) Content
from A
group by FormID;多列的话,按照格式自行添加即可
我假定现在你有两个字段,第一个字段的类型为char(2),第二个为varchar2(10)
declare
type ref_cur is ref cursor;
type my_array is table of varchar(31)
index by binary_integer;
fieldname my_array;
fieldindex integer;
my_cur ref_cur;
my_cur1 ref_cur;
SqlStr varchar2(1000);
temp my_source.col2%type;
temp1 my_source.col2%type;
begin
SqlStr := 'drop table my_dest';
execute immediate SqlStr;
SqlStr := 'Create table my_dest(col1 char(2)';
fieldindex := 1;
open my_cur for select distinct(col2) from my_source order by col2;
loop
fetch my_cur into temp;
exit when my_cur%notfound;
SqlStr := SqlStr||','||temp||' varchar2(10)';
fieldname(fieldindex) := temp;
fieldindex := fieldindex + 1;
end loop;
SqlStr := SqlStr ||')';
close my_cur;
execute immediate SqlStr;
open my_cur for select distinct(col1) from my_source order by col1;
loop
fetch my_cur into temp;
exit when my_cur%notfound;
SqlStr := 'insert into my_dest(col1) values('||''''||temp||''''||')';
execute immediate SqlStr;
dbms_output.put_line(SqlStr);
fieldindex := 1;
open my_cur1 for select col2 from my_source where col1 = temp order by col2;
loop
fetch my_cur1 into temp1;
exit when my_cur1%notfound;
SqlStr := 'update my_dest set '||fieldname(fieldindex)||'='||''''||temp1||''''||'where col1 = '||''''||temp||'''';
dbms_output.put_line(SqlStr);
execute immediate SqlStr;
fieldindex := fieldindex + 1;
end loop;
close my_cur1;
end loop;
close my_cur;
COMMIT;
end;