How to convert multiple rows into multiple columns in mysql

Mysql method to convert multiple rows into multiple columns: first query the data in the original table; then merge the multiple rows of data into the same column or different columns, such as [select name, group_concat(sore Separator '; ') as score from stu group by name].

Route transformation in MySQL

(Video tutorial recommendation: mysql video tutorial)

mysql row-column conversion is extremely frequently used in projects, especially reports in some financial projects. The most troublesome thing is converting multiple rows to multiple columns and dynamic column-row conversion. Recently, I have been studying these row conversions, and I started with the simplest row-column conversion.

First of all, the original table of sample data is as follows:

[Multiple rows into one column] (the merged data is in the same column):

select name ,group_concat(sore Separator ';') as score from stu group by name

[Multiple rows to multiple columns] (the merged data is on different columns):

SELECT name ,
MAX(CASE type WHEN '数学' THEN score ELSE 0 END ) math,
MAX(CASE type WHEN '英语' THEN score ELSE 0 END ) English ,
MAX(CASE type WHEN '语文' THEN score ELSE 0 END ) Chinese 
FROM stu  
GROUP BY name

The above is the detailed content of How to convert multiple rows into multiple columns in mysql. For more information, please follow other related articles on the PHP Chinese website!