Oracle Development topics: Row and column conversion __oracle

First, use the Decode function for row-column:

First of all, let's look at the most typical requirements: Student transcript report making

"1" Table structure definition: CREATE TABLE SCORE
(
ID Number (2),
Student_name VARCHAR2 (10),
SUBJECT VARCHAR2 (10),
SCORE Number (6, 2)
)
"2" test data are as follows: SQL > select * from Score;

ID student_name SUBJECT SCORE
-- -------- --------------- ---------- ----------
1 Paul Chinese 80
1 Paul Math 75.05
1 Paul 中文版 90
2 Bob Chinese 85.05
2 Bob 中文版 73.05
2 Bob Math 99

6 rows have been selected.
The results of the final conversion of "3" are as follows: ID student_name Grammar Math English
---------- --------------- ---------- ---------- ----------
1 Paul 80 75.05 90
2 Bob 85.05 99 73.05
"4" Implementation: SQL > select ID,
2 Student_name,
3 sum (decode (subject, ' Chinese ', score)) syntax,
4 sum (decode (subject, ' math ', score)) Mathematics,
5 sum (decode (subject, ' 中文版 ', score)) English
6 from Score
7 Group by ID, Student_name;

ID student_name Grammar Math English
-- -------- --------------- ---------- ---------- ----------
1 Paul 80 75.05 90
2 Bob 85.05 99 73.05

SQL >
The principle here is to use the Decode function to judge the column subject, if equal to ' Chinese ', then add the corresponding score column's value. The other two fields are equally known.

"5" Note:

This approach has several features:
① The fields used to judge all of its possible values must be known, and if it is unknown the SQL here will not be established.
② The fields used for conversion are usually numeric, because the case of a row-and-column conversion usually uses the cumulative sum
③ fields used for group by are primary key fields, not fields for judgment

"6" Extension requirements:

Assuming that we are not now to generate transcripts report, we have to count the number of people who have a reference to each subject, the final form of the results are as follows: SUBJECT STUDENT
------- ---------
Chinese Paul, Bob.
中文版 Paul, Bob
Math Paul, Bob.
In this case, we need to redefine Oracle's ' Sum ' function, which involves another topic (to be introduced later)

second, the use of Insert...select for "column career Change":

"1" Table structure definition: CREATE TABLE score_2
(
ID Number (2) primary key,
Student_name VARCHAR2 (10),
Chinese number (6, 2),
Math Number (6, 2),
中文版 Number (6, 2)
)
"2" test data are as follows: SQL > select * from score_2;

ID student_name Chinese MATH 中文版
-- -------- --------------- ---------- ---------- ----------
1 Paul 70 80 90
2 Bob 80 90 100
The results of the final conversion of "3" are as follows: SQL > select * from Score;

ID student_name SUBJECT SCORE
-- -------- --------------- ---------- ----------
1 Paul Chinese 80
1 Paul Math 75.05
1 Paul 中文版 90
2 Bob Chinese