Classic SQL problem: row to Column

The school records the results, each person's choice course is different, and later will add the course, so do not need to take all the courses as a column. Database grade inside the data, for example, assume that each person's name is different, as the primary key. This article is based on MySQL, and other databases will have a slightly different syntax.

Database data:

Post-processing effects:

Here are three ways to do this:

Method One:

SelectDistinct A.name, (selectscorefromgrade bwherea.name=b.nameandb.course= ' language ') as ' language ', (selectscorefromgrade bwherea.name=b.nameandb.course= ' math ') as ' math ', (selectscorefromgrade bwherea.name=b.nameandb.course= ' English ') as ' English ' Fromgrade A

Method Two:

Selectname,sum (case Coursewhen ' language ' thenscoreend) as ' language ', sum (case coursewhen ' math ' thenscoreend) as ' math ', sum (case Coursewhen ' English ' thenscoreend) as ' English ' Fromgradegroupbyname

Method Three:

DELIMITER &&create PROCEDURE sp_count () begin# course name declare course_n VARCHAR; #所有课程数量DECLARE Count int;# Counter declare i INT DEFAULT 0; #拼接SQL字符串SET @s = ' SELECT name '; SET count = (SELECT  count (distinct course) from grade); While I < count doset Course_n = (SELECT course from grade LIMIT i,1); SET @s = CONCAT (@s, ', SUM (case  Course "  , ' \ ', Course_n, ' \ ', ' then score END ') ', ' as ', ' \ ', Course_n, ' \ '); s ET i = i+1; END while; SET @s = CONCAT (@s, ' from grade GROUP by name '); #用于调试 #select @s; PREPARE stmt from @s; EXECUTE stmt; end&& call Sp_count ();

Method Analysis:

The first method uses table joins.
The second uses a grouping, which is processed separately for each grouping.
The third uses the stored procedure, is actually the second method dynamic, first calculates the quantity of all courses, then carries on the course query to each grouping.
It is obvious that the first two methods are hard-coded, and you need to modify the SQL after adding the course. And the third one doesn't have that kind of problem.

Note:

MySQL cannot remove another stored procedure in one stored procedure, only another stored procedure can be called
Originally wanted to write in method three: DROP PROCEDURE IF EXISTS sp_count (); This is wrong. Debugging when the wrong write, can only be manually deleted, and did not find a good method.

References:

• Relive sql--row to column, make a career change
• SQL statement for database row to column

2013-8-8 Update:

Method Two can also use the IF statement.
As shown below:

SELECT Name,sum (if (course = ' language ', score, null)) as ' language ', sum (if (course = ' math ', score, null)) as ' math ', sum (if (Cour SE = ' English ', score, null)) as ' English ' from grade GROUP by name

• if (EXPR1,EXPR2,EXPR3), if Expr1 is true (expr1<>0 and Expr1<>null), then if () returns EXPR2, otherwise it returns EXPR3. IF () returns a numeric or string value, depending on the context in which it is used.

Classic SQL problem: row to Column