About converting column values to column names in MySQL

Time:2021-9-1

We sometimes have this need:
About converting column values to column names in MySQL
This presentation related to column values is sometimes very intuitive. I will use a small demo to realize this kind of operation.


Table structure

create table demo1(
    Sname varchar (20) not null comment 'student',
    Course varchar (10) not null comment 'account',
    Score float not null comment 'score'
)

Insert the following data:

sname course score
Zhang San chinese 100
Zhang San mathematics 90
Zhang San English 80
Li Si chinese 90
Li Si mathematics 70
Li Si English 100

MySQL provides conditional branching syntax (similar to if / else, case…)

Syntax:
1. Case key when condition then result when condition then result… Else key (default to original) end
2. If (field as column = ‘value’, data field to be displayed, and other values)


Upper Code:

Select sname 'name',
        Max (if (course = 'Chinese', score, 0)) 'Chinese',
        AVG (case course when 'then score end)' Mathematics',
        Max (if (course = 'English', score, 0)) 'English' 
        from demo1 group by sname;
Execution results:

About converting column values to column names in MySQL


Summary:

Through the above SQL statement, we realized the conversion from row value to column name. We borrowed the aggregate function to realize this operation with the condition branch. Course = ‘language’ is the condition judgment, and score is the actual data field to be displayed. If the condition is not tenable, the output is 0. The use of aggregate function is not rigorous. In fact, AVG function can also be other aggregate functions.