oracle9数据库
现有这样一个表table1:
ID Name m1 m2
-----------------------------------
1 aaa am1 am2
2 bbb bm1 bm2
3 ccc cm1 cm2
4 ddd dm1 dm2
5 eee em1 em2
另外一个表table2不知道有没有用?
FIELD_ID FIELD_NAME
----------------------
1 m1
2 m2我要组织成如下信息:
FieldName ID Name FieldValue
----------------------------------------------------
m1 1 aaa am1
m2 1 aaa am2
m1 2 bbb bm1
m2 2 bbb bm2
m1 3 ccc cm1
m2 3 ccc cm2
m1 4 ddd dm1
m2 4 ddd dm2
m1 5 eee em1
m2 5 eee em2各位有什么好的想法?我先谢了!
现有这样一个表table1:
ID Name m1 m2
-----------------------------------
1 aaa am1 am2
2 bbb bm1 bm2
3 ccc cm1 cm2
4 ddd dm1 dm2
5 eee em1 em2
另外一个表table2不知道有没有用?
FIELD_ID FIELD_NAME
----------------------
1 m1
2 m2我要组织成如下信息:
FieldName ID Name FieldValue
----------------------------------------------------
m1 1 aaa am1
m2 1 aaa am2
m1 2 bbb bm1
m2 2 bbb bm2
m1 3 ccc cm1
m2 3 ccc cm2
m1 4 ddd dm1
m2 4 ddd dm2
m1 5 eee em1
m2 5 eee em2各位有什么好的想法?我先谢了!
left outer join
(select A.* from
((select id,name,m1 as fieldvalue,'m1' as ss from table1)
union all
(select id,name,m2 as fieldvalue,'m2' as ss from table1)) A) D)
on C.field_name=D.ss
create talbe #temp_TABLE
(
FieldName ID Name FieldValue
)
2.INSERT INTO #TEMP_TABLE
SELECT b.m1,a.id,a.name,a.m1 from table1 a,table2 b
insert into table3
select as FieldName,ID,Name,m1 as FieldValue
from table1insert into table3
select 'm2' as FieldName,ID,Name,m2 as FieldValue
from table1特别注意: 'm1' 的单引号不能去掉
insert into table3
select 'm1' as FieldName,ID,Name,m1 as FieldValue
from table1insert into table3
select 'm2' as FieldName,ID,Name,m2 as FieldValue
from table1特别注意: 'm1' 的单引号不能去掉