rt!
select '1','2','3','4','5' from table_x我想以这些列的数据向另一张表中插入key-value形式的数据 就是一个是列名字,一个是列值~请各位高人帮忙啊……………………
select '1','2','3','4','5' from table_x我想以这些列的数据向另一张表中插入key-value形式的数据 就是一个是列名字,一个是列值~请各位高人帮忙啊……………………
--是这个意思么?SQL*Plus: Release 8.0.6.0.0 - Production on 星期二 5月 24 12:21:41 2011(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> create table table_x as select '1' col1,'2' col2,'3' col3,'4' col4,'5' col5 from dual;Table created.SQL> create table table_y (key varchar2(20),val varchar2(20));Table created.SQL> /COL1 COL2 COL3 COL4 COL5
----- ----- ----- ----- -----
1 2 3 4 5SQL> select * from table_y;no rows selectedSQL> declare
2 val varchar2(100);
3 begin
4 for rs in (select distinct column_name from user_tab_columns where table_name='TABLE_X')
5 loop
6 execute immediate 'select '||rs.column_name||' from table_x' into val ;
7 insert into table_y values(rs.column_name, val) ;
8 end loop;
9 end;
10 /PL/SQL procedure successfully completed.SQL> select * from table_y;KEY VAL
---------- --------------
COL1 1
COL2 2
COL3 3
COL4 4
COL5 5SQL>
--10g
SQL> with t as(
2 select '10,20,30,40,50' a from dual)
3 select level||'-'||regexp_substr(a,'[^,]+',1,level) value
4 from t
5 connect by
6 level <= length(a) - length(replace(a,',',''))+1
7 /
VALUE
---------------------------------------------------------------------
1-10
2-20
3-30
4-40
5-50
另一张表中:
列1 , 列二
A 1
B 2
C 3大家有什么意见呢?
如果是多行多列,是不是每次都要查询 select col_name from t1 where rownum = 2 类似这种的语句?