Use dynamic row-to-column _ MySQL during MySQL storage

This article describes how to use dynamic row-to-column conversion in the MySQL stored procedure. For more information, see the examples described in this article. The following is an example of a simple database.

Data table structure

Here, I will use a simple example to illustrate it. it is also a classic example of row-to-column conversion, that is, the score of students.
Three tables:Student table, course list, and course list

Student table
To put it simply, there are two fields: student ID and student name.

Create table 'studen' ('stuid' VARCHAR (16) not null comment 'student id ', 'stamp' VARCHAR (20) not null comment 'student name ', primary key ('stuid') COLLATE = 'utf8 _ general_ci 'ENGINE = InnoDB;

Course Schedule
Course No. and course name

Create table 'course' ('courseno' VARCHAR (20) not null, 'coursenm' VARCHAR (100) not null, primary key ('courseno ')) COMMENT = 'Schedules 'COLLATE = 'utf8 _ general_ci' ENGINE = InnoDB;

Orders table
Student ID, course number, score

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;

The above is the structure of the database table. no foreign keys are created here. However, according to the table structure, we can clearly see that the student ID and course number in the student table and course table are associated with each other.

Data Preparation

/* Student table data */Insert Into student (stuid, stunm) Values ('000000', 'Zhang San'); Insert Into student (stuid, stunm) Values ('000000 ', 'Lee 4'); Insert Into student (stuid, stunm) Values ('123', 'Zhao 2'); Insert Into student (stuid, stunm) Values ('123 ', 'Wang 5'); Insert Into student (stuid, stunm) Values ('123', 'Liu qing'); Insert Into student (stuid, stunm) Values ('123 ', 'Zhou Ming');/* course table data */Insert Into courses (courseno, coursenm) Values ('c001', '文'); Insert Into courses (courseno, coursenm) values ('c002 ', 'New horizon English'); Insert Into courses (courseno, coursenm) Values ('c003 ', 'Discrete mat'); 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 ', 'Higher mathematics (1)'); Insert Into courses (courseno, coursenm) Values ('c007 ', 'Higher Mathematics (2 )'); /* Into table data */Insert Into score (stuid, courseno, scores) Values ('000000', 'c001', 67); Insert Into score (stuid, courseno, scores) values ('000000', 'c001', 68); Insert Into score (stuid, courseno, scores) Values ('000000', 'c001', 69 ); insert Into score (stuid, courseno, scores) Values ('000000', 'c001', 70); Insert Into score (stuid, courseno, scores) Values ('20140901 ', 'c001', 71); Insert Into score (stuid, courseno, scores) Values ('000000', 'c001', 72); Insert Into score (stuid, courseno, scores) values ('000000', 'c002', 87); Insert Into score (stuid, courseno, scores) Values ('000000', 'c002', 88 ); insert Into score (stuid, courseno, scores) Values ('000000', 'c002', 89); Insert Into score (stuid, courseno, scores) Values ('20140901 ', 'c002', 90); Insert Into score (stuid, courseno, scores) Values ('000000', 'c002', 91); Insert Into score (stuid, courseno, scores) values ('000000', 'c002', 92); Insert Into score (stuid, courseno, scores) Values ('000000', 'c003 ', 83 ); insert Into score (stuid, courseno, scores) Values ('000000', 'c003 ', 84); Insert Into score (stuid, courseno, scores) Values ('20140901 ', 'c003 ', 85); Insert Into score (stuid, courseno, scores) Values ('000000', 'c003', 86); Insert Into score (stuid, courseno, scores) values ('000000', 'c003 ', 87); Insert Into score (stuid, courseno, scores) Values ('000000', 'c003', 88 ); insert Into score (stuid, courseno, scores) Values ('000000', 'c004 ', 88); Insert Into score (stuid, courseno, scores) Values ('2013 ', 'c004 ', 89); Insert Into score (stuid, courseno, scores) Values ('000000', 'c004', 90); Insert Into score (stuid, courseno, scores) values ('000000', 'c004 ', 91); Insert Into score (stuid, courseno, scores) Values ('000000', 'c004', 92 ); insert Into score (stuid, courseno, scores) Values ('000000', 'c004 ', 93); Insert Into score (stuid, courseno, scores) Values ('2013 ', 'c005 ', 77); Insert Into score (stuid, courseno, scores) Values ('000000', 'c005', 78); Insert Into score (stuid, courseno, scores) values ('000000', 'c005 ', 79); Insert Into score (stuid, courseno, scores) Values ('000000', 'c005', 80 ); insert Into score (stuid, courseno, scores) Values ('000000', 'c005 ', 81); Insert Into score (stuid, courseno, scores) Values ('2013 ', 'c005 ', 82); Insert Into score (stuid, courseno, scores) Values ('000000', 'c006', 77); Insert Into score (stuid, courseno, scores) values ('20140901', 'c006 ', 78); Insert Into score (stuid, courseno, scores) Values ('20160901', 'c006', 79 ); insert Into score (stuid, courseno, scores) Values ('000000', 'c006 ', 80); Insert Into score (stuid, courseno, scores) Values ('20140901 ', 'c006 ', 81); Insert Into score (stuid, courseno, scores) Values ('000000', 'c006', 82 );

Why do you want to convert rows to columns?

This is the result of this column when we query the results, but in general, we want to see this result

The row-to-column operation is required for such results.

How to convert columns

As the result, we only need to do this for normal row-to-column conversion.

Static row-to-column conversion

Select st. stuid, st. stunm, MAX (CASE c. coursenm WHEN '文' THEN s. scores ELSE 0 END)' 文', 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 Matus' THEN ifnull (s. scores, 0) ELSE 0 END) 'discrete mates', 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 (1) 'THEN ifnull (s. scores, 0) ELSE 0 END) 'higher mathematics (1) ', MAX (CASE c. coursenm WHEN 'advanced mathematics (II) 'Then ifnull (s. scores, 0) ELSE 0 END) 'higher Mathematics (2) 'From Student stLeft Join score s On st. stuid = s. stuidLeft Join courses c On c. courseno = s. coursenoGroup by st. stuid

The above statement shows that we can use

MAX (CASE c. coursenm WHEN 'linear algebra 'THEN ifnull (s. scores, 0) ELSE 0 END) 'Linear algebra ',

This statement is used to convert rows into columns.

However, we all know that the course is not just a few courses. if you use the above statements, you must first determine the number of courses you have, in that case, write a query statement, but there will be a lot to write. So I want to avoid dynamic row-to-column operations? Of course the answer is yes!

Dynamic row-to-column conversion

So how to perform dynamic row-to-column conversion?

First, we need to dynamically obtain such statements.

MAX (CASE c. coursenm WHEN '文' THEN s. scores ELSE 0 END)' 文', MAX (CASE c. coursenm WHEN 'linear algebra 'THEN ifnull (s. scores, 0) ELSE 0 END) 'Linear algebra ', MAX (CASE c. coursenm WHEN 'discrete Matus' THEN ifnull (s. scores, 0) ELSE 0 END) 'discrete Matus'

Instead of writing a sentence like above, how can we get such a statement?

SQL statement splicing is required here. Specifically, the following statements

SELECT GROUP_CONCAT(DISTINCT  CONCAT(   'MAX(IF(c.coursenm = ''',   c.coursenm,   ''', s.scores, 0)) AS ''',   c.coursenm, ''''  ) )FROM courses c;

The result is

MAX (IF (c. coursenm = 'University Chinese ', s. scores, 0) AS '文', MAX (IF (c. coursenm = 'new horizon English ', s. scores, 0) AS 'new horizon English ', MAX (IF (c. coursenm = 'discrete Matus', s. scores, 0) AS 'discrete mat', MAX (IF (c. coursenm = 'probability theory and mathematical statistics', s. scores, 0) AS 'probability theory and mathematical statistics', MAX (IF (c. coursenm = 'linear algebra ', s. scores, 0) AS 'linear algebra ', MAX (IF (c. coursenm = 'higher mathematics (1) ', s. scores, 0) AS 'higher mathematics (1) ', MAX (IF (c. coursenm = 'higher Mathematics (2) ', s. scores, 0) AS 'higher Mathematics (2 )'

Yes, that is, the statements used for row-to-column query above, so we don't need to know how many courses and the names of these courses, just a few lines of code can get dynamic columns.

Dynamic columns are obtained. how can I query the results with SQL statements?
It should be noted here, because the concatenation function is used. if the preceding query statements are used, replace those statements with the following:

Select st.stuid, st.stunm, (  SELECT   GROUP_CONCAT(DISTINCT    CONCAT(     'MAX(IF(c.coursenm = ''',     c.coursenm,     ''', s.scores, NULL)) AS ',     c.coursenm    )   )  FROM courses c)From Student stLeft Join score s On st.stuid = s.stuidLeft Join courses c On c.courseno = s.coursenoGroup by st.stuid;

However, the result is as follows:

I will not go into details here, so I think everyone understands. So what should we do if this is not the case?

That's right. here we need to declare the statement like the common statements, splice the statement into a complete one, and then execute the statement, that is, the following:

SET @sql = NULL;SELECT GROUP_CONCAT(DISTINCT  CONCAT(   'MAX(IF(c.coursenm = ''',   c.coursenm,   ''', s.scores, 0)) AS ''',   c.coursenm, ''''  ) ) INTO @sqlFROM courses c;SET @sql = CONCAT('Select st.stuid, st.stunm, ', @sql,             ' 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');PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;

Execute these statements directly and obtain the following results.

That's right. it's the same as the statement that was all spelled out at the beginning, so that dynamic row-to-column conversion is achieved. In addition, we do not need to know how many courses or list these course names one by one.

Of course, the query in this statement concatenation can be added to the condition query. for example, we want to query the score of 1003 for the student ID.
That is, the following:

The statement is as follows:

SET @sql = NULL;SET @stuid = '1003';SELECT GROUP_CONCAT(DISTINCT  CONCAT(   'MAX(IF(c.coursenm = ''',   c.coursenm,   ''', s.scores, 0)) AS ''',   c.coursenm, ''''  ) ) INTO @sqlFROM courses c;SET @sql = CONCAT('Select st.stuid, st.stunm, ', @sql,             ' From Student st             Left Join score s On st.stuid = s.stuid            Left Join courses c On c.courseno = s.courseno            Where st.stuid = ''', @stuid, '''            Group by st.stuid');PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;

Compared with the preceding statement, we can see that I have changed some values after the Left join operation in the second row, and the previous variable added a @ stuid. [note: here, the @ symbol is used to define variables in SQL statements. I personally think it should be used to differentiate them.]

So the question is, the row-to-column query has been implemented. how does the title still contain the stored procedure? That's right. it's a stored procedure!

Like the preceding statement, if we operate in MySQL directly, but if we use the project, we obviously cannot use this statement, in addition, my project is to use MyBatis in combination. we all know that you can write SQL statements in the XML file of MyBatis, but obviously we cannot put them in XML files.

The most important thing is that the If condition cannot be used here. For example, we need to determine whether the student ID is null or equal to 0 and then add the condition for query. However, it is not supported here.
That's right.

SET @sql = NULL;SET @stuid = '1003';SET @courseno = 'C002';SELECT GROUP_CONCAT(DISTINCT  CONCAT(   'MAX(IF(c.coursenm = ''',   c.coursenm,   ''', s.scores, 0)) AS ''',   c.coursenm, ''''  ) ) INTO @sqlFROM courses c;SET @sql = CONCAT('Select st.stuid, st.stunm, ', @sql,             ' From Student st             Left Join score s On st.stuid = s.stuid            Left Join courses c On c.courseno = s.courseno');            IF @stuid is not null and @stuid != 0 thenSET @sql = CONCAT(@sql, ' Where st.stuid = ''', @stuid, '''');END IF;  SET @sql = CONCAT(@sql, ' Group by st.stuid');PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;

Yes. after I add if, people do not support it. it is so capricious.

Therefore, we need to use stored procedures, and the advantage of using stored procedures is that it is convenient for us to call, which is equivalent to a function. other similar queries do not need to repeatedly write code, it's good to directly tune the stored procedure and add the if condition as needed. haha ~.

So how should we write the stored procedure here?
I will not write more statements for creating a stored procedure. here, I can directly put the preceding query statements into the begin and end of the stored procedure, as shown below:

DELIMITER && drop procedure if exists SP_QueryData;Create Procedure SP_QueryData(IN stuid varchar(16))READS SQL DATA BEGINSET @sql = NULL;SET @stuid = NULL;SELECT GROUP_CONCAT(DISTINCT  CONCAT(   'MAX(IF(c.coursenm = ''',   c.coursenm,   ''', s.scores, 0)) AS ''',   c.coursenm, '\''  ) ) INTO @sqlFROM courses c;SET @sql = CONCAT('Select st.stuid, st.stunm, ', @sql,             ' From Student st             Left Join score s On st.stuid = s.stuid            Left Join courses c On c.courseno = s.courseno');            IF stuid is not null and stuid <> '' thenSET @stuid = stuid;SET @sql = CONCAT(@sql, ' Where st.stuid = \'', @stuid, '\'');END IF;  SET @sql = CONCAT(@sql, ' Group by st.stuid');PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;END && DELIMITER ;

Well, compared with the preceding simple SQL statement, we can see that the if statement is used to determine the student ID.

However, it should be noted that the if statement here is not the same as the method we usually use in java.

If (condition)
{
Statement block to be executed
}
Yes, the if statements in SQL are different, and no parentheses or anything is required, just like speaking English directly.

IF @stuid is not null and @stuid != 0 thenSET @sql = CONCAT(@sql, ' Where st.stuid = ''', @stuid, '''');END IF;

Well, it's so simple and clear. if conditions are met, then what will happen and then it will end.

Then we can pass the parameter to call this SP.

CALL `SP_QueryData`('1001');

The following result is displayed.

Of course, we can also directly upload an empty string.

CALL `SP_QueryData`('');

Get the expected results.

Well, the above is the implementation process of dynamic row-to-column conversion in MySQL.

Summary and problems

At the beginning, I only wanted to convert the rows into columns and write the statements that suddenly found to be dynamic, because I was not sure how many columns there were.
I am not very familiar with searching for information on the Internet!

Later, I wrote it based on external table with dynamic columns in MySQL.

Then there are various problems. First, the if condition is added to the SQL statement. I found that it is useless as I usually write java.

IF(stuid is not null && stuid <> '') thenSET @stuid = stuid;SET @sql = CONCAT(@sql, ' Where st.stuid = \'', @stuid, '\'');END IF;

However, I did not use it after writing it. I still reported an error. after finding a lot, I found that this was not the case. then I changed it.

After the change, I thought it was okay, but I still found that it was not good. Then I was wondering if it could not be judged by if. because it was not a function or procedure, I wrote the statement for creating procedure.

After the transformation, procedure is successfully created. After the creation, I will try again. after procedure is called, Dangdang will get the result.

Well, this process is still a lot of gains, for MySQL row-to-column, as well as the storage process, as well as the use of SQL statements in different places.
In addition, after the implementation of this row-to-column conversion, this project basically has no big problem in data processing, which is quite good, haha ~

The above is all the content in the implementation process of row-to-column conversion. Relatively speaking, I think it is very clear here, so if you have the patience to read and study it carefully, this content is of great benefit to your row-to-column conversion.

The above is the use of dynamic row-to-column _ MySQL content in the MySQL stored procedure. For more information, see The PHP Chinese network (www.php1.cn )!