You can use the decode function to convert columns in Oracle:
If there is A student Table:
ID |
NAME |
SUBJECT |
SCORE |
1 |
Zhang San |
Chinese |
90 |
2 |
Zhang San |
Mathematics |
80 |
3 |
Li Si |
Chinese |
99 |
4 |
Li Si |
Mathematics |
78 |
5 |
Zhang San |
English |
89 |
Now you want to convert it to the following table:
NAME |
Chinese |
Mathematics |
English |
Zhang San |
90 |
80 |
89 |
Li Si |
99 |
78 |
|
This is a typical row-to-column conversion process. You only need the following SQL:
Select NAME,Sum (decode (SUBJECT, 'China', SCORE, null )),Sum (decode (SUBJECT, 'mat', SCORE, null )),Sum (decode (SUBJECT, 'English ', SCORE, null ))From A group by NAME;
About Join:
1. inner join:
Select * from A a, B B where a. id = B. aId and
Select * from A a inner join B B on a. id = B. aId is the same;
2. left join and left outer join:
Select * from A a left join B B on (a. id = B. aId );
In this case, all records in Table A will be found no matter whether there are any records corresponding to A in Table B;