A case study of mysql changing from multiple rows to multiple columns

This article mainly introduces mysql multi-row multi-column case analysis, with a certain reference value, friends can refer to the need. I hope you all have a lot of success after reading this article. Let's take a look at it together.

Column and column transformation in MySQL

mysql column conversion is extremely frequently used in projects, especially in financial reports. One of the most headache is multi-row to multi-column, dynamic column and row conversion. A recent study of these transitions in rows begins with the simplest transitions between rows and columns.

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

[Multiple rows to one column](consolidated data in the same column):

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

[Multiple rows to multiple columns](consolidated data in different columns):

SELECT name ,MAX(CASE type WHEN 'DIGITAL' THEN score ELSE 0 END ) math,MAX(CASE type WHEN 'ENGLISH' THEN score ELSE 0 END ) English ,MAX(CASE type WHEN 'ENGLISH' THEN score ELSE 0 END ) Chinese FROM stu GROUP BY name

Thank you for reading this article carefully. I hope that Xiaobian will share the case analysis content of mysql multi-row and multi-column to help everyone. At the same time, I hope that everyone will support more, pay attention to the industry information channel, find out if you encounter problems, and find detailed solutions waiting for you to learn!