mysql row-column conversion is very frequently used in projects, especially in some financial projects. Among them, the most headache is multi-row to multi-column, dynamic row to row conversion. Recent studies of these row-to-column transformations have begun with the simplest row-to-column transformations.

sql script

-- Create table student table
CREATE TABLE `student` (
    `stuid` VARCHAR(16) NOT NULL COMMENT 'Student ID',
    `stunm` VARCHAR(20) NOT NULL COMMENT 'Student name',
    PRIMARY KEY (`stuid`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB; 


-- Class Schedule Card 

CREATE TABLE `courses` (
    `courseno` VARCHAR(20) NOT NULL,
    `coursenm` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`courseno`)
)
COMMENT='Class Schedule Card'
COLLATE='utf8_general_ci'
ENGINE=InnoDB;


-- Score sheet
CREATE TABLE `score` (
    `stuid` VARCHAR(16) NOT NULL,
    `courseno` VARCHAR(20) NOT NULL,
    `scores` FLOAT NULL DEFAULT NULL,
    PRIMARY KEY (`stuid`, `courseno`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

-- insert data

-- Student table data

Insert Into student (stuid, stunm) Values('1001', 'Zhang San');
Insert Into student (stuid, stunm) Values('1002', 'Li Si');
Insert Into student (stuid, stunm) Values('1003', 'Zhao er');
Insert Into student (stuid, stunm) Values('1004', 'Wang Wu');
Insert Into student (stuid, stunm) Values('1005', 'Liu Qing');
Insert Into student (stuid, stunm) Values('1006', 'Zhou Ming');

-- Course schedule data 
Insert Into courses (courseno, coursenm) Values('C001', 'College Chinese');
Insert Into courses (courseno, coursenm) Values('C002', 'New Horizon English');
Insert Into courses (courseno, coursenm) Values('C003', 'discrete mathematics');
Insert Into courses (courseno, coursenm) Values('C004', 'Probability Theory and Mathematical Statistics');
Insert Into courses (courseno, coursenm) Values('C005', 'linear algebra');
Insert Into courses (courseno, coursenm) Values('C006', 'Advanced mathematics(One)');
Insert Into courses (courseno, coursenm) Values('C007', 'Advanced mathematics(Two)');

-- Achievement sheet data

Insert Into score(stuid, courseno, scores) Values('1001', 'C001', 67);
Insert Into score(stuid, courseno, scores) Values('1002', 'C001', 68);
Insert Into score(stuid, courseno, scores) Values('1003', 'C001', 69);
Insert Into score(stuid, courseno, scores) Values('1004', 'C001', 70);
Insert Into score(stuid, courseno, scores) Values('1005', 'C001', 71);
Insert Into score(stuid, courseno, scores) Values('1006', 'C001', 72);
Insert Into score(stuid, courseno, scores) Values('1001', 'C002', 87);
Insert Into score(stuid, courseno, scores) Values('1002', 'C002', 88);
Insert Into score(stuid, courseno, scores) Values('1003', 'C002', 89);
Insert Into score(stuid, courseno, scores) Values('1004', 'C002', 90);
Insert Into score(stuid, courseno, scores) Values('1005', 'C002', 91);
Insert Into score(stuid, courseno, scores) Values('1006', 'C002', 92);
Insert Into score(stuid, courseno, scores) Values('1001', 'C003', 83);
Insert Into score(stuid, courseno, scores) Values('1002', 'C003', 84);
Insert Into score(stuid, courseno, scores) Values('1003', 'C003', 85);
Insert Into score(stuid, courseno, scores) Values('1004', 'C003', 86);
Insert Into score(stuid, courseno, scores) Values('1005', 'C003', 87);
Insert Into score(stuid, courseno, scores) Values('1006', 'C003', 88);
Insert Into score(stuid, courseno, scores) Values('1001', 'C004', 88);
Insert Into score(stuid, courseno, scores) Values('1002', 'C004', 89);
Insert Into score(stuid, courseno, scores) Values('1003', 'C004', 90);
Insert Into score(stuid, courseno, scores) Values('1004', 'C004', 91);
Insert Into score(stuid, courseno, scores) Values('1005', 'C004', 92);
Insert Into score(stuid, courseno, scores) Values('1006', 'C004', 93);
Insert Into score(stuid, courseno, scores) Values('1001', 'C005', 77);
Insert Into score(stuid, courseno, scores) Values('1002', 'C005', 78);
Insert Into score(stuid, courseno, scores) Values('1003', 'C005', 79);

-- 
select st.stuid,st.stunm  from student  st 

select sc.stuid , sc.courseno,sc.scores  from score sc 

select  cs.courseno,cs.coursenm   from courses cs

Requirements: Query each student's course and grade

 select   st.stuid ID ,  st.stunm Full name, cs.coursenm Course name ,sc.scores achievement     from  student st, score sc ,courses cs

where st.stuid = sc.stuid and sc.courseno = cs.courseno  

Result:

This is four columns and twenty-seven rows.

We turn rows into columns, ID corresponds to name, each course corresponds to each grade.

Static row exclusive

 select st.stuid number, st.stunm Full name ,
Max(case c.coursenm when 'College Chinese' then s.scores else 0 end ) 'College Chinese',
max(case c.coursenm when 'New Horizon English' then IFNULL(s.scores,0)else 0 end) 'New Horizon English',
Max(case c.coursenm when 'discrete mathematics' then IFNULL(s.scores,0) ELSE 0 END) 'discrete mathematics',
MAX(case c.coursenm when 'Probability Theory and Mathematical Statistics' then IFNULL(s.scores,0) else 0 end) 'Probability Theory and Mathematical Statistics',
MAX(case c.coursenm  when 'linear algebra' then IFNULL(s.scores,0) else 0 END) 'linear algebra',
MAX(case c.coursenm when 'Advanced mathematics(One)' THEN IFNULL(s.scores,0) else 0 end) 'Advanced mathematics(One)',
MAX(case c.coursenm when 'Advanced mathematics(Two)' THEN IFNULL(s.scores,0) else 0 end) 'Advanced mathematics(Two)'
from  student st 
LEFT JOIN score s on st.stuid = s.stuid
LEFT JOIN courses c on c.courseno = s.courseno
GROUP BY st.stuid

Look again at the results of the operation:

This is the implementation of simple columns.

Let's look at the group_concat() function again.

group_concat(), which is described in the manual as returning string results with non-NULL values from a connection from a group.
It is abstract and difficult to understand.

Popular understanding is that group_concat() calculates which rows belong to the same group and displays the columns that belong to the same group. Which columns to return, by letter

Number parameters (that is, field names) are determined. Grouping must have a standard, which is grouping according to the column specified by group by.

These are explanations from the Internet, but they are still not easy to understand. Let's go directly to the code and see the results of run, and then look back on the results after run! __________

Selects. stuid number, GROUP_CONCAT(courseno) course number, GROUP_CONCAT(s.scores) score from scores GROUP BY. stuid

Look at the results of the operation:
Previous effects:

It is very obvious that GROUP_CONCAT() has the function of putting together the result set of course number courseno and score.