Classic SQL problem: row to Column

Source: Internet
Author: User

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, (selectscorefromgrade ' language ') as ' language ', (selectscorefromgrade ' math ') as ' math ', (selectscorefromgrade ' 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.


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.


    • 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.