Oracle动态生成查询交叉表

表结构:

序号
 字段  中文名称        类型           说        明
   1   ReportID   NUMBER(4)     报告编号

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为需要查询的报告号

再下面为其它查询条件

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为需要查询的报告号

再下面为其它查询条件

服务器租用/服务器托管中国五强！虚拟主机域名注册顶级提供商！15年品质保障！--亿恩科技[ENKJ.COM]