How to implement row-column conversion in mysql

MySQL is a popular relational database management system. It provides powerful row-column conversion functions, allowing users to operate data more conveniently. What is row-column conversion? Row-column conversion refers to converting row data in the table into column data, or converting column data into row data. This article will introduce how to use MySQL's row-column conversion function.

1. Convert row data into column data

In MySQL, we can use the PIVOT and UNPIVOT functions to convert row data in the table into column data. The PIVOT function converts the row data in the table into column data, while the UNPIVOT function converts the column data in the table into row data.

Below is an example. Suppose we have a table students, which contains students' names, subjects and grades.

##小红English85

Student Name	Subject	Score
Xiao Ming	Mathematics	90
小明	English	80
小红	MATHEMATICS	95

We can use the PIVOT function to convert the student's grades into the following form:

Student NameMathematics ScoreEnglish score##Xiao Ming小红##Use the following code to implement:

SELECT
  学生姓名,
  MAX(CASE WHEN 科目 = '数学' THEN 成绩 ELSE NULL END) AS 数学成绩,
  MAX(CASE WHEN 科目 = '英语' THEN 成绩 ELSE NULL END) AS 英语成绩
FROM students
GROUP BY 学生姓名;

90	80
95	85

Among them, The MAX function is used to obtain the maximum score value. If the condition is not met, NULL is returned.

Convert column data into row data

2. Corresponding to the PIVOT function is the UNPIVOT function, which is used to convert column data in the table into row data. Below is an example. Suppose we have a table sales, which contains the sales records of each salesperson each month.

Salesman1 Month Sales2 Month Sales3 Month Sales小明100002000015000小Red120001800011000## We can use the UNPIVOT function to convert the sales record into the following form:

SalespersonMonth Xiao Ming1月2月3月##小红1月 12000小红February1800011000 Use the following code to implement: Among them, the FOR clause in the UNPIVOT function is used to specify the Converted columns. In this example, we specify that the columns to be converted are [January], [February], and [March]. Summary

Sales
10000		小明
20000		小明
15000
	##小红	March
		SELECT   销售员,   MONTH,   销售额 FROM sales UNPIVOT(   销售额 FOR MONTH IN ([1月], [2月], [3月]) ) AS unpvt;

Route conversion is a very practical function, especially in scenarios related to reports and business data analysis. MySQL provides the PIVOT and UNPIVOT functions to make data conversion more convenient for users. Being able to use these two functions proficiently will bring great convenience to your database operations.

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