MySQL implements column to row and row to column operation code

introduction

When processing data, we often encounter situations where we need to convert columns (fields) in a table into rows, or convert rows into columns. This operation is often called "column to row" and "row to column" (Unpivoting). In MySQL, although keywords such as PIVOT and UNPIVOT are not directly provided, we can use other methods to implement these functions. This article will introduce you to how to use CASE statements, aggregate functions, and GROUP BY clauses to complete column-to-row and row-to-column operations.

Column to line (Pivoting)

Column to row refers to converting the values ​​of one or more columns in a table into new column titles and filling the corresponding data into these new columns. The following is an example to illustrate this process.

Sample data

Suppose there is a score sheetscores, including the student's namename,sujectsubjectand scorescore：

CREATE TABLE scores (
    name VARCHAR(50),
    subject VARCHAR(20),
    score INT
);

INSERT INTO scores (name, subject, score) VALUES
('Alice', 'Math', 95),
('Alice', 'English', 88),
('Bob', 'Math', 76),
('Bob', 'English', 92);

Query results before conversion

SELECT * FROM scores;
+-------+---------+-------+
| name  | subject | score |
+-------+---------+-------+
| Alice | Math    |    95 |
| Alice | English |    88 |
| Bob   | Math    |    76 |
| Bob   | English |    92 |
+-------+---------+-------+

Column to line SQL statements

We need tosubjectThe different values ​​of the column are changed to new column names and the corresponding column names are changed.scoreFill in.

SELECT
    name,
    MAX(CASE WHEN subject = 'Math' THEN score ELSE NULL END) AS Math,
    MAX(CASE WHEN subject = 'English' THEN score ELSE NULL END) AS English
FROM
    scores
GROUP BY
    name;

Convert query results

+-------+------+---------+
| name  | Math | English |
+-------+------+---------+
| Alice |   95 |      88 |
| Bob   |   76 |      92 |
+-------+------+---------+

Row to column (Unpivoting)

Row to column is the inverse process of column to row, that is, converting the data of multiple columns into a row and multiple records. This can be achieved through UNION ALL.

Sample data

Suppose there is another table nowstudents, it has stored student information in the form of column-to-profession conversion:

CREATE TABLE students (
    name VARCHAR(50),
    Math INT,
    English INT
);

INSERT INTO students (name, Math, English) VALUES
('Alice', 95, 88),
('Bob', 76, 92);

Query results before conversion

SELECT * FROM students;
+-------+------+---------+
| name  | Math | English |
+-------+------+---------+
| Alice |   95 |      88 |
| Bob   |   76 |      92 |
+-------+------+---------+

Row to column SQL statements

We turn the grades of each subject into a separate line of records.

SELECT
    name,
    'Math' AS subject,
    Math AS score
FROM
    students
UNION ALL
SELECT
    name,
    'English' AS subject,
    English AS score
FROM
    students;

Convert query results

+-------+---------+-------+
| name  | subject | score |
+-------+---------+-------+
| Alice | Math    |    95 |
| Bob   | Math    |    76 |
| Alice | English |    88 |
| Bob   | English |    92 |
+-------+---------+-------+

Through the above example, we can see how to flexibly convert column to row and row to column data in MySQL. Hopefully these tips can help you better manage and analyze data in your database.

This is the article about MySQL's operation code for column-to-line and row-to-column conversion. For more related contents of MySQL column-to-row and row-to-column conversion, please search for my previous articles or continue browsing the following related articles. I hope everyone will support me in the future!