表为
year renkou baifenbi
2001 10w 2%
2002 12w 5%
2003 13w 6%
转换为
2001 2002 2003
10w 12w 13w
2% 5% 6%第二个表的列数取决于第一个表的行数,如何实现,请大家帮忙了!!!!
year renkou baifenbi
2001 10w 2%
2002 12w 5%
2003 13w 6%
转换为
2001 2002 2003
10w 12w 13w
2% 5% 6%第二个表的列数取决于第一个表的行数,如何实现,请大家帮忙了!!!!
还是先输出到页面,再在页面重组了~~:)
您给我的帖子,是一般的需求,我这里的需求跟那个不同的。不过还是先谢谢您了
create or replace PROCEDURE row_change_column(tab_name VARCHAR2) as
type store_column_name_type is table of VARCHAR2(10) index by PLS_INTEGER;
store_column_name store_column_name_type;
type row_column_info_type is table of store_column_name_type index by PLS_INTEGER;
row_column_info row_column_info_type;
sql_statemant VARCHAR2(100);
cursor column_cursor is select column_name from sys.DBA_TAB_COLUMNS
where upper(table_name) = upper(tab_name);
i number :=1 ;
begin
open column_cursor;
loop
fetch column_cursor into store_column_name(i);
exit when column_cursor%NOTFOUND;
i := i+1;
end loop;
close column_cursor;
for i in 1 .. store_column_name.count loop
/*DBMS_OUTPUT.put_line(store_column_name(i));*/
sql_statemant := 'select '||store_column_name(i)||' from '||tab_name;
EXECUTE IMMEDIATE sql_statemant
bulk collect into row_column_info(i) ;
end loop;
for k in reverse 1 .. row_column_info.count loop
for j in 1 .. row_column_info(k).count loop
DBMS_OUTPUT.put(rpad(row_column_info(k)(j),5)|| ' ');
end loop;
DBMS_OUTPUT.put_line('one row end.');
end loop;
end;
/
show error