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]