Detailed examples of converting rows to columns and columns to rows in MySQL

mysql row to column, column to row

The sentence is not difficult, so I won't explain it in detail. When reading the sentence, analyze it sentence by sentence from the inside out.

Row to Column

There is a table as shown in the figure. Now we want to convert the rows into columns in the query result.

The table creation statement is as follows:

CREATE TABLE `TEST_TB_GRADE` (
 `ID` int(10) NOT NULL AUTO_INCREMENT,
 `USER_NAME` varchar(20) DEFAULT NULL,
 `COURSE` varchar(20) DEFAULT NULL,
 `SCORE` float DEFAULT '0',
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

insert into TEST_TB_GRADE(USER_NAME, COURSE, SCORE) values
("Zhang San", "Mathematics", 34),
("Zhang San", "Chinese", 58),
("Zhang San", "English", 58),
("Li Si", "Mathematics", 45),
("Li Si", "Chinese", 87),
("Li Si", "English", 45),
("Wang Wu", "Mathematics", 76),
("Wang Wu", "Chinese", 34),
("Wang Wu", "English", 89);

Query statement:

The reason why MAX is used here is to set the points with no data to 0 to prevent NULL

SELECT user_name ,
  MAX(CASE course WHEN '数学' THEN score ELSE 0 END ) Mathematics,
  MAX(CASE course WHEN 'Chinese' THEN score ELSE 0 END ) Chinese,
  MAX(CASE course WHEN 'English' THEN score ELSE 0 END ) English FROM test_tb_grade
GROUP BY USER_NAME;

Results:

Column to Row

There is a table as shown in the figure. Now we hope that the query results will be listed in rows

The table creation statement is as follows:

CREATE TABLE `TEST_TB_GRADE2` (
 `ID` int(10) NOT NULL AUTO_INCREMENT,
 `USER_NAME` varchar(20) DEFAULT NULL,
 `CN_SCORE` float DEFAULT NULL,
 `MATH_SCORE` float DEFAULT NULL,
 `EN_SCORE` float DEFAULT '0',
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

insert into TEST_TB_GRADE2(USER_NAME, CN_SCORE, MATH_SCORE, EN_SCORE) values
("Zhang San", 34, 58, 58),
("Li Si", 45, 87, 45),
("Wang Wu", 76, 34, 89);

Query statement:

select user_name, 'Language' COURSE , CN_SCORE as SCORE from test_tb_grade2
union select user_name, '数学' COURSE, MATH_SCORE as SCORE from test_tb_grade2
union select user_name, 'English' COURSE, EN_SCORE as SCORE from test_tb_grade2
order by user_name,COURSE;

Results:

Thank you for reading, I hope it can help you, thank you for your support of this site!

You may also be interested in:

Simple implementation of SQL row to column and column to row
SQL row to column and column to row code explanation
The ultimate solution for converting rows to columns and columns to rows in mssql database
SQL row to column and column to row

<<: JavaScript typing game

>>: Linux forced release of occupied ports and Linux firewall port opening method detailed explanation