Oracle動態(tài)生成查詢交叉表

表結(jié)構(gòu):

序號
 字段  中文名稱        類型           說        明
   1   ReportID   NUMBER(4)     報告編號

REPORTID RECORDNO FIELDNO DATUM
      1                1              1         王小強
      1                1              2         武漢市江漢區(qū)王家路
      1                1              3         2010-01-12
      1                1              4         65783213
      1                1              5         否
      1                2              1         劉節(jié)章 
      1                2              2         武昌區(qū)中南路
      1                2              3         1950-04-12
      1                2              4         13999999999
      1                2              5         已婚
 

Oracle動態(tài)生成查詢交叉表

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         武漢市江漢區(qū)王家路
      1                1              3         2010-01-12
      1                1              4         65783213
      1                1              5         否
      1                2              1         劉節(jié)章 
      1                2              2         武昌區(qū)中南路
      1                2              3         1950-04-12
      1                2              4         13999999999
      1                2              5         已婚
 

Oracle動態(tài)生成查詢交叉表

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年品質(zhì)保障！--億恩科技[ENKJ.COM]