Oracle 11g row-and-column Conversion

This article provides a simple example of Oracle 11g row/column conversion for beginners.

The existing problems are as follows:

Problem description:
Table1
Id Name
1 Taylor
2 Jim

Table 2
FId value attr
1 23 age
1 boy sex
2 26 age
2 boy sex

Table1 Id is the primary key, and Table2's FId is the foreign key, corresponding to the primary key of Table1

Name of the person whose age is greater than 24 and whose sex is boy
The result is:
Name
Jim

Create Table disease insert data. TABLE1 is the source table and TABLE2 is the target table. The script is as follows:

Create table table2 (
Fid number (12 ),
Value varchar2 (10 ),
Attr varchar2 (10)
);

Insert into table2 values (1, '23', 'age ');
Insert into table2 values (1, 'boys', 'sex ');
Insert into table2 values (2, '26', 'age ');
Insert into table2 values (2, 'boys', 'sex ');


Create table table1 (
Id number (12 ),
Name varchar2 (20)
);

Insert into table1 values (1, 'taylor ');
Insert into table1 values (2, 'Jim ');
The following statement converts the rows and columns of table 1:

Select *
From table2 trim (max (value) as attr for (attr) in ('age' as age,
'Sex' as sex ));

The following statement can be used to answer the initial question:

With effect_table2 (
Select *
From table2 trim (max (value) as attr for (attr) in ('age' as age,
'Sex 'as sex )))
Select t1. * from effect_table2 pt, table1 t1
Where pt. fid = t1.id
And pt. age_attr> 24 and pt. sex_attr = 'boys'