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.
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.
Student Name | Subject | Score |
---|---|---|
Xiao Ming | Mathematics | 90 |
小明 | English | 80 |
小红 | MATHEMATICS | 95 |
English | 85 |
Mathematics Score | English score | |
---|---|---|
90 | 80 | |
95 | 85 |
Convert column data into row data
2 Month Sales | 3 Month Sales | ||
---|---|---|---|
20000 | 15000 | 小Red | |
18000 | 11000 | ## We can use the UNPIVOT function to convert the sales record into the following form: |
Sales | ||
---|---|---|
10000 | 小明 | |
20000 | 小明 | |
15000 | ##小红 | 1月 |
小红 | February | |
##小红 | March | |
Use the following code to implement: | SELECT 销售员, MONTH, 销售额 FROM sales UNPIVOT( 销售额 FOR MONTH IN ([1月], [2月], [3月]) ) AS unpvt; Copy after login | 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].
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!