How to convert rows to columns in MySQL

How to convert rows to columns in MySQL

MySQL row to column operation

The so-called row-to-column operation is to convert the row information of a table into column information. It may be a bit general. Here is an example, as follows:

+----+-----------+--------+-------+
| ID | USER_NAME | COURSE | SCORE |
+----+-----------+--------+-------+
| | Zhang San| Mathematics| |
| | Zhang San| Chinese| |
| | Zhang San| English| |
| | Li Si| Mathematics| |
| | Li Si| Chinese| |
| | Li Si| English| |
| | Wang Wu| Mathematics| |
| | Wang Wu| Chinese| |
| | Wang Wu| English| |
+----+-----------+--------+-------+
 rows in set (0.00 sec)

+-----------+--------+--------+--------+
| user_name | Mathematics| Chinese| English|
+-----------+--------+--------+--------+
| Zhang San| | | |
| Li Si| | | |
| Wang Wu| | | |
+-----------+--------+--------+--------+
 rows in set (0.00 sec)

In the above example, Table 1 gives the three grades of three students, while Table 2 converts the row record information (subject, name) of Table 1 into column information and displays them in groups according to different user_name.

1 case when operation method

To achieve the above function, we need to perform analysis. First, we need to generate three columns, namely mathematics, Chinese and English, and then fill in the corresponding data for the values ​​in each column. Here we need to use the MySQL case when then end operation, which is a conditional operation. Regarding this conditional statement, first we give an explanation:

case colume 
  when condition1 then result1
  when condition2 then result2
  when condition3 then result3
else result4
end

The above syntax can be understood as when the value of column meets condition1, use result1 to replace the value of column, and so on. When the column value does not meet the condition, use result4 to replace the value of column.

Now let’s start the experiment:

First we create a table and insert the following data:

mysql-yeyz ::>>select * from test_tbl;
+----+-----------+--------+-------+
| ID | USER_NAME | COURSE | SCORE |
+----+-----------+--------+-------+
| | Zhang San| Mathematics| |
| | Zhang San| Chinese| |
| | Zhang San| English| |
| | Li Si| Mathematics| |
| | Li Si| Chinese| |
| | Li Si| English| |
| | Wang Wu| Mathematics| |
| | Wang Wu| Chinese| |
| | Wang Wu| English| |
+----+-----------+--------+-------+
 rows in set (0.00 sec)

According to the case when syntax above, when the course is 'Mathematics', we define a column 'Mathematics' and fill it with its score. If we encounter 'Chinese' or 'English', we replace it with 0. We can first write the following SQL in general:

mysql-yeyz ::>>SELECT user_name ,
(CASE course WHEN '数学' THEN score ELSE END ) 数学FROM test_tbl;
+-----------+--------+
| user_name | Mathematics |
+-----------+--------+
| Zhang San| |
| Zhang San| |
| Zhang San| |
| Li Si| |
| Li Si| |
| Li Si| |
| Wang Wu| |
| Wang Wu| |
| Wang Wu| |
+-----------+--------+
 rows in set (0.00 sec)

We find that the table above has only two columns. According to SQL rules, we can add the values ​​of 'Chinese' and 'English' to it and write several more columns at once, as follows:

mysql-yeyz ::>>SELECT user_name ,
  -> (CASE course WHEN 'mathematics' THEN score ELSE END ) Mathematics,
  -> (CASE course WHEN 'Chinese' THEN score ELSE END ) Chinese,
  -> (CASE course WHEN 'English' THEN score ELSE END ) English-> FROM test_tbl;
+-----------+--------+--------+--------+
| user_name | Mathematics| Chinese| English|
+-----------+--------+--------+--------+
| Zhang San| | | |
| Zhang San| | | |
| Zhang San| | | |
| Li Si| | | |
| Li Si| | | |
| Li Si| | | |
| Wang Wu| | | |
| Wang Wu| | | |
| Wang Wu| | | |
+-----------+--------+--------+--------+
 rows in set (0.00 sec)

Now we have all the records and are almost reaching our goal. Let's see the difference with the final result:

+-----------+--------+--------+--------+
| user_name | Mathematics| Chinese| English|
+-----------+--------+--------+--------+
| Zhang San| | | |
| Li Si| | | |
| Wang Wu| | | |
+-----------+--------+--------+--------+

It seems that all that is left is to merge the information of students with the same name. Naturally, we think of the group_by (user_name) operation, and the group_by operation needs to be combined with some aggregate functions (MAX, MIN, AVG, SUM, COUNT, etc.). Since each record only contains the score of the current subject and the scores of other subjects are 0, the results when we use the MAX function and the SUM function are the same, but the AVG function and the MIN function cannot be used. This should be easy to understand.

Below we give the final result:

mysql-yeyz 13:55:52>>SELECT user_name ,
  -> MAX(CASE course WHEN '数学' THEN score ELSE END ) Mathematics,
  -> MAX(CASE course WHEN 'Chinese' THEN score ELSE END ) Chinese,
  -> MAX(CASE course WHEN 'English' THEN score ELSE END ) English-> FROM test_tbl
  -> GROUP BY USER_NAME;
+-----------+--------+--------+--------+
| user_name | Mathematics| Chinese| English|
+-----------+--------+--------+--------+
| Zhang San | 34 | 58 | 58 |
| Li Si | 45 | 87 | 45 |
| Wang Wu| 76 | 34 | 89 |
+-----------+--------+--------+--------+
3 rows in set (0.00 sec)



mysql-yeyz ::>>SELECT user_name ,
  -> sum(CASE course WHEN '数学' THEN score ELSE END ) Mathematics,
  -> sum(CASE course WHEN 'Chinese' THEN score ELSE END ) Chinese,
  -> sum(CASE course WHEN 'English' THEN score ELSE END ) English-> FROM test_tbl
  -> GROUP BY USER_NAME;
+-----------+--------+--------+--------+
| user_name | Mathematics| Chinese| English|
+-----------+--------+--------+--------+
| Zhang San | 34 | 58 | 58 |
| Li Si | 45 | 87 | 45 |
| Wang Wu| 76 | 34 | 89 |
+-----------+--------+--------+--------+
3 rows in set (0.00 sec)


mysql-yeyz ::>>SELECT user_name ,
  -> MIN(CASE course WHEN 'mathematics' THEN score ELSE END ) Mathematics,
  -> MIN(CASE course WHEN 'Chinese' THEN score ELSE END ) Chinese,
  -> MIN(CASE course WHEN 'English' THEN score ELSE END ) English-> FROM test_tbl
  -> GROUP BY USER_NAME;
+-----------+--------+--------+--------+
| user_name | Mathematics| Chinese| English|
+-----------+--------+--------+--------+
| Zhang San | 0 | 0 | 0 |
| Li Si| 0 | 0 | 0 |
| Wang Wu| 0 | 0 | 0 |
+-----------+--------+--------+--------+
3 rows in set (0.00 sec)

It can be seen that the results of using MAX and SUM are the same, but using MIN as the aggregate function will result in the final output result being 0, because the minimum value of the subject specified by the user_name is selected each time, which is 0. This result is easy to understand.

2 if operation method

The above case when operation method is understood, then the if operation method is also easy to understand. The principle is the same, except that the syntax of case when is converted to if method, as follows

mysql-yeyz 14:12:42>>SELECT user_name ,
  -> MAX(if (course= 'Mathematics',score,) ) Mathematics,
  -> MAX(if (course= 'Chinese',score,) ) Chinese,
  -> MAX(if (course= 'English',score,) ) English-> FROM test_tbl
  -> GROUP BY USER_NAME;
+-----------+--------+--------+--------+
| user_name | Mathematics| Chinese| English|
+-----------+--------+--------+--------+
| Zhang San | 34 | 58 | 58 |
| Li Si | 45 | 87 | 45 |
| Wang Wu| 76 | 34 | 89 |
+-----------+--------+--------+--------+
3 rows in set (0.00 sec)

3 Add a total column

After we have implemented the basic row-to-column conversion, we now need to add a total field to the converted table. We can add this field in the following way, that is, when we start counting, we also count the score value, as follows:

mysql-yeyz 14:18:06>>SELECT user_name ,
  -> (CASE course WHEN 'mathematics' THEN score ELSE END ) Mathematics,
  -> (CASE course WHEN 'Chinese' THEN score ELSE END ) Chinese,
  -> (CASE course WHEN 'English' THEN score ELSE END ) English,
  -> (score) total
  -> FROM test_tbl;
+-----------+--------+--------+--------+-------+
| user_name | Mathematics| Chinese| English| total |
+-----------+--------+--------+--------+-------+
| Zhang San | 34 | 0 | 0 | 34 |
| Zhang San | 0 | 58 | 0 | 58 |
| Zhang San | 0 | 0 | 58 | 58 |
| Li Si | 45 | 0 | 0 | 45 |
| Li Si | 0 | 87 | 0 | 87 |
| Li Si | 0 | 0 | 45 | 45 |
| Wang Wu | 76 | 0 | 0 | 76 |
| Wang Wu| 0 | 34 | 0 | 34 |
| Wang Wu| 0 | 0 | 89 | 89 |
+-----------+--------+--------+--------+-------+
9 rows in set (0.00 sec)

The above results are not aggregated. It should be noted that if we want to aggregate, the first three columns can use the sum or max method, and the last column must use the sum method, because we require the total score, and using the max method will cause the value to become the value with the highest score. The final sql is as follows:

mysql-yeyz 14:18:29>>SELECT user_name ,
  -> sum(CASE course WHEN '数学' THEN score ELSE END ) Mathematics,
  -> sum(CASE course WHEN 'Chinese' THEN score ELSE END ) Chinese,
  -> sum(CASE course WHEN 'English' THEN score ELSE END ) English,
  -> sum(score) total
  -> FROM test_tbl
  -> GROUP BY USER_NAME;
+-----------+--------+--------+--------+-------+
| user_name | Mathematics| Chinese| English| total |
+-----------+--------+--------+--------+-------+
| Zhang San | 34 | 58 | 58 | 150 |
| Li Si | 45 | 87 | 45 | 177 |
| Wang Wu | 76 | 34 | 89 | 199 |
+-----------+--------+--------+--------+-------+
3 rows in set (0.00 sec)

4 Simple method group_concat

If we are not so particular about the display format of the results, we can also use a rough method, which is the group_concat function, to write all the columns together and represent them in one field. The effect is as follows:

mysql-yeyz 14:19:13>>SELECT user_name,
GROUP_CONCAT(`course`,":",score)AS score FROM test_tbl 
GROUP BY user_name;
+-----------+------------------------------+
| user_name | grades |
+-----------+------------------------------+
| Zhang San | Mathematics: 34, Chinese: 58, English: 58 |
| Li Si | Mathematics: 45, Chinese: 87, English: 45 |
| Wang Wu | Mathematics: 76, Chinese: 34, English: 89 |
+-----------+------------------------------+
3 rows in set (0.00 sec)

This method is equivalent to directly grouping the original table, and can also cope with certain application scenarios.

The above is the detailed content of the method of converting rows to columns in MySQL. For more information about MySQL row to column conversion, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:
  • MySQL row to column details
  • How to convert a column of comma-separated values ​​into columns in MySQL
  • Detailed examples of converting rows to columns and columns to rows in MySQL
  • Using dynamic row to column conversion in MySQL stored procedure
  • MySQL row to column and column to row

<<:  Detailed explanation of the Docker container lifecycle architecture and the differences between it and VM

>>:  Why is there this in JS?

Recommend

Solve the problem that PhpStorm fails to connect to VirtualBox

Problem description: When phpstorm's SFTP hos...

Solve the problem that the name of the type=file file modification form cannot be echoed normally

The code under the easyui framework is as follows...

Example of using CASE WHEN in MySQL sorting

Preface In a previous project, the CASE WHEN sort...

Learn about JavaScript closure functions in one article

Table of contents Variable Scope The concept of c...

Detailed explanation of how to use $props, $attrs and $listeners in Vue

Table of contents background 1. Document Descript...

Usage and difference analysis of replace into and insert into on duplicate key update in MySQL

This article uses examples to illustrate the usag...

Examples of using html unordered list tags and ordered list tags

1. Upper and lower list tags: <dl>..</dl...

Use Grafana+Prometheus to monitor MySQL service performance

Prometheus (also called Prometheus) official webs...

MYSQL8.0.13 free installation version configuration tutorial example detailed explanation

1. Download, I take 8.0 as an example Download ad...

Basic usage analysis of Explain, a magical tool for MySQL performance optimization

Introduction MySQL provides an EXPLAIN command th...

JavaScript to achieve dynamic table effect

This article shares the specific code for JavaScr...

Example of how to set WordPress pseudo-static in Nginx

Quoting Baidu's explanation of pseudo-static:...

How to manage users and groups when running Docker

Docker is a management tool that uses processes a...

Complete steps for Docker to pull images

1. Docker pull pulls the image When using $ docke...

Nest.js hashing and encryption example detailed explanation

0x0 Introduction First of all, what is a hash alg...