Home > Database > Mysql Tutorial > How to implement row-column conversion in mysql

How to implement row-column conversion in mysql

PHPz
Release: 2023-04-17 16:47:49
Original
3179 people have browsed it

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 学生姓名;
Copy after login
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

  1. 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;
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template