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?

Detailed process of installing Presto and connecting Hive in Docker

How to convert rows to columns in MySQL

Detailed process of installing Presto and connecting Hive in Docker

HTML+CSS implementation code for rounded rectangle

HTML+CSS3 code to realize the animation effect of the solar system planets

Prometheus monitors MySQL using grafana display

Detailed explanation of the underlying principle of defineCustomElement added in vue3.2

What knowledge systems do web designers need?

How to use vs2019 for Linux remote development

Sorting out some common problems encountered in CSS (Hack logo/fixed container/vertical centering of images)

Detailed tutorial of using stimulsoft.reports.js with vue-cli

Detailed explanation of Nginx http resource request limit (three methods)

Recommend

Detailed explanation of Mencached cache configuration based on Nginx

MySQL lock control concurrency method

MySQL full-text fuzzy search MATCH AGAINST method example

MySQL 8.0.18 installation and configuration graphic tutorial

JavaScript gets the scroll bar position and slides the page to the anchor point

Introduction to HTML method of opening link files using hyperlinks

Web page color matching example analysis: Green color matching web page analysis

Native js to realize bouncing ball

JavaScript implements asynchronous acquisition of form data

Analysis of the principle of Vue nextTick

Detailed explanation of the meaning and difference between MySQL row locks and table locks

Mycli is a must-have tool for MySQL command line enthusiasts

How to select all child elements and add styles to them in CSS

Detailed explanation of the mysqlslap command and syntax for the built-in stress test in MySQL 5.7

Deepin20.1 system installs MySQL8.0.23 (super detailed MySQL8 installation tutorial)