Oracle动态生成查询交叉表

表结构:

序号
 字段  中文名称        类型           说        明
   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         13995094532
      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为需要查询的报告号

再下面为其它查询条件