SQL face question-row and column interchange-if, "case when"

Http://www.cda.cn/view/21469.html

In Tb_lemon_grade, the table field Id,student_name,course,score represents the score ID, student name, course name, course score, and table 1. Please write out a SQL to change the data in table 1 into the form of table 2
ID Student Name Course title Course Results
1 Three Linux 85
2 three MySQL 92
3 sheets of three Java 87
4 John Doe Linux 96
5 John Doe MySQL 89
6 John Doe Java 100
7 Harry Linux 91
8 Harry MySQL 83
9 Harry Java 98
Table 1
Student name Linux MySQL Java
Sheet 385 92 87
Lee 496 89 100
Wang 591 83 98
Table 2
One: Create a table
CREATE TABLE Tb_lemon_grade (
ID INT (Ten) not NULL auto_increment PRIMARY KEY,
Student_name VARCHAR (DEFAULT) NULL,
Course VARCHAR (DEFAULT) NULL,
Score FLOAT DEFAULT ' 0 ');
Second: Initialize data
INSERT into Tb_lemon_grade (Student_name, course, score) VALUES
("Zhang San", "Linux", 85),
("Zhang San", "MySQL", 92),
("Zhang San", "Java", 87),
("John Doe", "Linux", 96),
("John Doe", "MySQL", 89),
("John Doe", "Java", 100),
("Harry", "Linux", 91),
("Harry", "MySQL", 83),
("Harry", "Java", 98);
Three: First we query out all the data, this result and our Figure 1 is the same
SELECT * from Tb_lemon_grade;

Four: Use constant columns to output our target structure
We can see that the results are very close to our figure two.

Five: Use the IF function, replace our constant column, assign the result to the corresponding column of the corresponding row
SELECT Student_name,
IF (COURSE = ' Linux ', score,0) ' Linux ',
IF (COURSE = ' mysql ', score,0) ' MySQL ',
IF (COURSE = ' java ', score,0) ' Java '
From Tb_lemon_grade;
Run SQL and the results are as follows:

Six: Let's analyze this result set,
In the original structure, each line represents a classmate of a certain section of the results, with the first behavior example, the first line is Zhang San students linux scores, so we result in a centralized Linux score of 85, while the other two columns MySQL and Java as a constant column, the score is 0.
Then analyze all the rows of each student's scores, as shown in each block containing the line, there is the student of this course results, and the rest of the box in the score value of 0. Therefore, it is not difficult to think that we can use groups to extract the results of each section by grouping

Seven: grouping, using the Max function to remove the maximum
(because only one row of the results is true, the other row is 0, so the maximum is the real score)
SELECT Student_name,
Max (IF (COURSE = ' Linux ', score,0)) ' Linux ',
Max (if (COURSE = ' mysql ', score,0)) ' MySQL ',
MAX (if (COURSE = ' java ', score,0)) ' Java '
from TB _lemon_grade
GROUP by Student_name;

Eight: You can also group, sum each row of data, use the SUM function, and the statements and results are as follows:
SELECT student_name,
SUM (IF (COURSE = ' Linux ', score,0)) ' Linux ',
SUM (if (COURSE = ' mysql ', score,0)) ' MySQL ',
sum (if (COURSE = ' java ', score,0)) ' Java '
from Tb_lemon_grade
GROUP by Student_name;

Nine: Since using the IF statement can achieve the effect, the same effect is used with the case statement
Grouping, using the max aggregation function
SELECT student_name,
Max (case COURSE when ' Linux ' Then score ELSE 0 end) as ' Linux ',
Max (case COURSE "MySQL" then score ELSE 0 END) as ' MySQL ',
Max (case COURSE W Hen ' Java ' then score ELSE 0 END] as ' Java '
from Tb_lemon_grade
GROUP by Student_name;
The result is as follows:

Using sum, the results are as shown
SELECT Student_name,
SUM (case COURSE if ' Linux ' then score ELSE 0 END) as ' Linux ',
SUM (case COURSE "MySQL" then score ELSE 0 END) as ' MySQL ',
SUM (case COURSE If ' Java ' then score ELSE 0 END) as ' Java '
From Tb_lemon_grade
GROUP by Student_name;

SQL face question-row and column interchange-if, "case when"