SQL Row to Column

TurnClassic SQL issues: Row to column posted 5 month ago (2015-09-19 17:49) Read (2855) | Reviews0） I want to collect this article .likes0

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 #课程名称 DECLARE course_n VARCHAR (20); #所有课程数量 DECLARE Count INT; #计数器 DECLARE i INT DEFAULT 0; #拼接SQL字符串 SET @s = ' SELECT name '; SET count = (SELECT count (distinct course) from grade); While I < count does SET Course_n = (SELECT course from grade LIMIT i,1); SET @s = CONCAT (@s, ', SUM (case course ", ' \ ', Course_n, ' \ ', ' then score END) ', ' as ', ' \ ', Course_n, ' \ '); SET 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.

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 (c Ourse = ' 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.

SQL Row to Column