SQL row to column (and EAV model fetch data)

Source: Internet
Author: User

Reference article:

Http://www.williamsang.com/archives/1508.html

Scenario Brief

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:

12345 SELECT DISTINCTa.name,(SELECT score FROM grade b WHERE a.name=b.name AND b.course=‘语文‘ ) AS ‘语文‘,(SELECT score FROM grade b WHERE a.name=b.name AND b.course=‘数学‘ ) AS ‘数学‘,(SELECT score FROM grade b WHERE a.name=b.name AND b.course=‘英语‘ ) AS ‘英语‘FROM grade a

Method Two:

12345 SELECT name,SUM(CASEcourse WHEN‘语文‘ THEN score END ) AS ‘语文‘,SUM(CASEcourse WHEN‘数学‘ THEN score END ) AS ‘数学‘,SUM(CASEcourse WHEN‘英语‘ THEN score END ) AS ‘英语‘FROM grade GROUP BY name

Method Three:

1234567891011121314151617181920212223242526 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 = (SELECTCOUNT(distinct course) FROM grade);WHILE i < count DOSET course_n = (SELECT course FROM grade LIMIT i,1);SET @s = CONCAT(@s, ‘, SUM(CASE  course WHEN  ‘,‘\‘‘, 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.

Resources:

    • 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:

12345 SELECT name,SUM(IF (course = ‘语文‘ , score , null ) ) as ‘语文‘,SUM(IF (course = ‘数学‘ , score , null ) ) as ‘数学‘,SUM(IF (course = ‘英语‘ , score , null ) ) as ‘英语 ‘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 number or string value, depending on whether it is used

Method Four: Use multiple self-joins (one more inner join for each row of rows)

SELECT ' Main_table '.*,' table_name '.' Value ' As ' Name ',' Table_name2 '.' Value ' As ' Url_path 'From ' Catalog_category_entity ' As ' Main_table 'INNER JOIN ' Catalog_category_entity_varchar ' As ' table_name ' On ' table_name '.' entity_id '=' Main_table '.' entity_id 'INNER JOIN ' Catalog_category_entity_varchar ' As ' Table_name2 ' On ' Table_name2 '.' entity_id '=' Main_table '.' entity_id 'WHERE 1=1and (' table_name '.' attribute_id ' = (41))and (' Table_name2 '.' attribute_id ' = (()) and (' main_table ').  ' parent_id ' = (2))GROUP by  main_table.  entity_id                 

SQL row to column (and EAV model fetch data)

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: info-contact@alibabacloud.com 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.