SQL row to column (and EAV model fetch data)

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)