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: