Sample code sharing to implement mysql row to column and column to row conversion

This article mainly introduces mysql row to column and column to row examples of detailed explanation of relevant information, friends in need can refer to

mysql row to column, column to row

The statement is not difficult, so I won’t explain it too much. When reading the statement, analyze it sentence by sentence from the inside out

Row to column

                                                                                                                                                                                                       that are currently expected to have the rows converted into columns as a result of the query and now wish to convert the rows into columns. Query statement:

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

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;

Result display:

Column to row

There is a table as shown in the figure, and now I hope that the query results will be listed in rows

The table creation statement is as follows:

insert into TEST_TB_GRADE(USER_NAME, COURSE, SCORE) values
("张三", "数学", 34),
("张三", "语文", 58),
("张三", "英语", 58),
("李四", "数学", 45),
("李四", "语文", 87),
("李四", "英语", 45),
("王五", "数学", 76),
("王五", "语文", 34),
("王五", "英语", 89);

SELECT user_name ,
  MAX(CASE course WHEN '数学' THEN score ELSE 0 END ) 数学,
  MAX(CASE course WHEN '语文' THEN score ELSE 0 END ) 语文,
  MAX(CASE course WHEN '英语' THEN score ELSE 0 END ) 英语
FROM test_tb_grade
GROUP BY USER_NAME;

Query statement:

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;

Results display:

The above is the detailed content of Sample code sharing to implement mysql row to column and column to row conversion. For more information, please follow other related articles on the PHP Chinese website!