Dynamic query crosstab chart generated by Oracle

Table Structure:

Serial number
Field Chinese name type description
1 ReportID NUMBER (4) Report No.
2 RecordNo NUMBER Record NUMBER
3 FieldNo NUMBER (2) field NUMBER
4 Datum VarChar (200) Value
 

REPORTID RECORDNO FIELDNO DATUM
1 1 1 Wang Xiaoqiang
1 1 2 Wangjia Road, Jianghan district, Wuhan
1 1 3 2010-01-12
1 1 4 65783213
1 1 5 NO
1 2 1 Liu chapter
1 2 Wuchang District Zhongnan Road
1 2 3 1950-04-12
1 2 4 13999999999
1 2 5 married
 

Dynamic query crosstab chart generated by Oracle

Select max (a1) as name, max (a2) as address, max (a3) as birth, max (a4) as phone, max (a5) as marital status

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 'wuhan % '))

Group by recordno

Reportid = 1 is the report number to be queried

The following are other query conditions: