表结构:
序号
字段 中文名称 类型 说 明
1 ReportID NUMBER(4) 报告编号
2 RecordNo NUMBER 记录号
3 FieldNo NUMBER(2) 字段编号
4 Datum VarChar(200) 值
REPORTID RECORDNO FIELDNO DATUM
1 1 1 王小强
1 1 2 武汉市江汉区王家路
1 1 3 2010-01-12
1 1 4 65783213
1 1 5 否
1 2 1 刘节章
1 2 2 武昌区中南路
1 2 3 1950-04-12
1 2 4 13999999999
1 2 5 已婚
Oracle动态生成查询交叉表
select max(a1) as 姓名, max(a2) as 地址, max(a3) as 出生, max(a4) as 电话, max(a5) as 婚否
from (select recordno,
nvl(decode(fieldno, 1, Datum), '') a1,
nvl(decode(fieldno, 2, Datum), '') a2,
nvl(decode(fieldno, 3, Datum), '') a3,
nvl(decode(fieldno, 4, Datum), '') a4,
nvl(decode(fieldno, 5, Datum), '') a5
from Rep_Register
where reportid = 1
and RecordNo = (select RecordNo
from Rep_Register
where FieldNo = 2
and Datum Like '武汉市%'))
group by recordno
reportid=1为需要查询的报告号
再下面为其它查询条件