表1
name age address gender phone
张3 12 北京 1 10000000
李4 20 天津 1 12390000
王5 18 北京 0 13200000转成表2
Name code value
张3 age 12
张3 gender 1
张3 phone 10000000
李4 age 20
李4 gender 1
李4 phone 12390000
王5 age 18
王5 gender 0
王5 phone 13200000请教了,小弟急用!!!!!!!!!!!!!!
name age address gender phone
张3 12 北京 1 10000000
李4 20 天津 1 12390000
王5 18 北京 0 13200000转成表2
Name code value
张3 age 12
张3 gender 1
张3 phone 10000000
李4 age 20
李4 gender 1
李4 phone 12390000
王5 age 18
王5 gender 0
王5 phone 13200000请教了,小弟急用!!!!!!!!!!!!!!
union
select Name,'gender ',gender from tb
union...
declare n number;
begin
select count(*) into n from user_tables where table_name=upper('T');
if n>0 then
execute immediate 'drop table T';
end if;
end;
Create table T
as
select '张3' name ,12 age ,'北京' address,1 gender,10000000 phone from dual union all
select '李4',20,'天津',1,12390000 from dual union all
select '王5',18,'北京',0,13200000 from dual
;Select * from Tselect Name ,'code' code,age from T
union
select Name,'gender',gender from T
union
select name,'phone',phone from T--结果:
1 李4 code 20
2 李4 gender 1
3 李4 phone 12390000
4 王5 code 18
5 王5 gender 0
6 王5 phone 13200000
把表T用函数变量代替, 写个过程, 你试一下看行不
from user_tab_cols
where table_name = '表1'循环生成字符串:
select Name,'age',age from '表1'
union
select Name,'gender ',gender from '表1'
union...执行字符串得到结果试试,可能行
select a.name,
case when b.rn = 1 then 'age'
when b.rn = 2 then 'gender'
when b.rn = 3 then 'telephone'
end as code,
case when b.rn = 1 then a.age
when b.rn = 2 then a.gender
when b.rn = 3 then a.telephone
end as a
from (
select a.name, a.age, a.address, a.gender, a.telephone
from table a
) a ,
(select cast(row_number() over(order by id) as number) as rn
from temp--借助一张含有至少4条记录的表,来生成一个序列或者借助dual表来生成序列也可
where rownum < 4
) b
order by 1
(
user_name VARCHAR2,
table1 VARCHAR2,
table2 VARCHAR2
) IS
col_name VARCHAR2(128);
sql_stat VARCHAR2(1000); CURSOR vi_cursor(tablese VARCHAR2, users VARCHAR2) IS
SELECT column_name
FROM dba_tab_columns
WHERE table_name = upper(tablese)
AND owner = upper(users);
BEGIN OPEN vi_cursor(table1, user_name); LOOP
FETCH vi_cursor
INTO col_name;
EXIT WHEN vi_cursor%NOTFOUND;
sql_stat := 'INSERT INTO ' || table2 || ' select Name, ''' ||
col_name || ''', ' || col_name || ' from ' || table1;
EXECUTE IMMEDIATE sql_stat;
END LOOP; CLOSE vi_cursor;
END getval;从数据字典里面找表的各个列,在根据列名查询一下应该就可以~
调用上面的过程,根据需要改动一下sql_stat应该符合LZ的要求CREATE TABLE TAB2(
"NAME" VARCHAR2(100),
"CODE" VARCHAR2(100),
"VAL" VARCHAR2(100));CALL KGWTESTER.getVal('KGWTESTER', 'tab1', 'tab2');select * from tab2;
decode(b.rn, 1, 'age', 2, 'gender', 3, 'telephone', 4, 'address') as code,
decode(b.rn,
1,
to_char(a.age),
2,
to_char(a.gender),
3,
to_char(a.phone),
4,
a.address) as value
from (select a.name, a.age, a.address, a.gender, a.phone from t a) a,
(select rownum as rn from dual connect by rownum < 5) b
order by 1;
--运行结果:
NAME CODE VALUE
1 李4 age 20
2 李4 gender 1
3 李4 address 天津
4 李4 telephone 12390000
5 王5 address 北京
6 王5 age 18
7 王5 gender 0
8 王5 telephone 13200000
9 张3 age 12
10 张3 address 北京
11 张3 gender 1
12 张3 telephone 10000000