Use dynamic row-column _mysql in Mysql stored procedures

The example described in this article successfully implements the dynamic row-column. Below I take a simple database as an example, illustrate.

Data table structure

Here I use a relatively simple example to illustrate, is also a classic example of row transfer, is the student's performance
Three tables: student table, timetable, score sheet

Student table
to be simple, student number, student name two fields

CREATE TABLE ' student ' (
  ' stuid ' VARCHAR not null COMMENT ' learner number ',
  ' stunm ' VARCHAR ' not null COMMENT ' student name ', 
   primary KEY (' Stuid ')
)
collate= ' utf8_general_ci '
engine=innodb;

Curriculum
Course number, course name

CREATE TABLE ' courses ' (
  ' Courseno ' VARCHAR not NULL,
  ' Coursenm ' VARCHAR (m) not NULL,
  PRIMARY KEY (' Courseno ')
comment= ' timetable '
collate= ' utf8_general_ci '
engine=innodb;

Score Sheet
Student Study number, course number, Grade

CREATE TABLE ' score ' (
  ' stuid ' VARCHAR not NULL,
  ' Courseno ' VARCHAR () not NULL,
  ' scores ' FLOAT null DEFA ULT NULL,
  PRIMARY KEY (' Stuid ', ' Courseno ')
)
collate= ' utf8_general_ci ' Engine=innodb
;

The above is the structure of the database table, there is no foreign key, but according to the structure of the table, you can clearly see the academic number and the course number in the score sheet is related to the student table, curriculum, respectively.

Data preparation

/* Student Table Data * * Insert into student (Stuid, STUNM) Values (' 1001 ', ' John ');
Insert into student (Stuid, STUNM) Values (' 1002 ', ' Dick ');
Insert into student (Stuid, STUNM) Values (' 1003 ', ' Zhao II ');
Insert into student (Stuid, STUNM) Values (' 1004 ', ' Harry ');
Insert into student (Stuid, STUNM) Values (' 1005 ', ' Liu ');
Insert into student (Stuid, STUNM) Values (' 1006 ', ' Zhou Ming ');
/* SCHEDULE Data * * * Insert into courses (Courseno, COURSENM) Values (' C001 ', ' university language ');
Insert into courses (Courseno, COURSENM) Values (' C002 ', ' New Horizons 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 ', ' higher mathematics (i) ');
Insert into courses (Courseno, COURSENM) Values (' C007 ', ' Advanced Mathematics (ii) ');
/* Score Table 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);
Insert into score (Stuid, Courseno, scores) Values (' 1004 ', ' C005 ', 80);
Insert into score (Stuid, Courseno, scores) Values (' 1005 ', ' C005 ', 81);
Insert into score (Stuid, Courseno, scores) Values (' 1006 ', ' C005 ', 82); Insert into Score (Stuid, courseNo, scores) Values (' 1001 ', ' C006 ', 77);
Insert into score (Stuid, Courseno, scores) Values (' 1002 ', ' C006 ', 78);
Insert into score (Stuid, Courseno, scores) Values (' 1003 ', ' C006 ', 79);
Insert into score (Stuid, Courseno, scores) Values (' 1004 ', ' C006 ', 80);
Insert into score (Stuid, Courseno, scores) Values (' 1005 ', ' C006 ', 81);
 Insert into score (Stuid, Courseno, scores) Values (' 1006 ', ' C006 ', 82);

Why row to Column

This is the result of this column we saw in our performance query, but in general we want to see this result

So the result is going to be a row-and-column operation.

How do I go to columns?

Like get the result of the above diagram, the General row-column, we just need to do this

Static Row-Column

Select St.stuid, ST.STUNM, 
  max (case C.coursenm ' University language ' THEN s.scores ELSE 0 end) ' University language ',
  max (case c.coursenm When ' New Horizons English ' THEN ifnull (s.scores,0) ELSE 0 end) ' New Horizons English ', 
  MAX (case c.coursenm when ' discrete math ' THEN ifnull (s.scores,0) ELS E 0 End) ' Discrete mathematics ',
  max (case c.coursenm when ' Probability theory and 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 ' higher mathematics (i) ' THEN ifnull (S.sco res,0) ELSE 0 end) ' Advanced Mathematics (A) ',
  MAX (case c.coursenm when ' advanced Mathematics (ii) ' THEN ifnull (s.scores,0) Else 0 end) ' Advanced Mathematics (ii) '
F Rom Student St left
join score s in St.stuid = S.stuid left
join courses c on C.courseno = S.courseno
Group by St.stuid

Looking at the above statement, we can see that after we know the fixed course, we can use

MAX (case c.coursenm when ' linear algebra ' THEN ifnull (s.scores,0) ELSE 0 end) ' Linear algebra ',

Such statements to implement row-column

But we all know, the curriculum is not only these few doors, if use the above statement to write, the first to determine how many courses, so many courses of course name to be taken out again, so write a query statement down, but to write a lot. So you want to be able to dynamically row-column operations? The answer, of course, is yes!

Dynamic Row-Column

So how do I make a dynamic row-column?

First we need to dynamically get such statements

Max (case c.coursenm when ' university language ' THEN s.scores ELSE 0 end) ' College language ',
max (case c.coursenm when ' linear algebra ' THEN ifnull (s.score s,0) ELSE 0 end) ' linear algebra ', 
MAX (case c.coursenm when ' discrete math ' THEN ifnull (s.scores,0) Else 0 end) ' Discrete mathematics '

Instead of writing it out like the one above, how do you get the sentence?

Here you will use the SQL statement stitching. Specifically, the following statement

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 = ' College language ', S.scores, 0)) as ' university language ',
max (if (c.coursenm = ' New Horizons English ', s.scores, 0)) as ' New Horizons English ',
max (if (C.coursenm = ' discrete mathematics ', s.scores, 0))  As ' discrete mathematics ',
max (if (c.coursenm = ' probability theory and 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 (i) ', S.scores, 0)) as ' higher Mathematics (a) ',
max (if (c.coursenm = ' higher Mathematics (ii) ', S.scores, 0) ) as ' advanced Mathematics (ii) '

Yes, that's right, that's the statement we're going to use to do a row-and-column query, so that you don't have to know how many courses and the names of these courses are, as long as a few lines of code can get a dynamic column.

Dynamic column is got, then how to combine the SQL statement query to get the results?
Here to illustrate a point, because the use of the concatenation function, if like the above query statement, just to replace those lines of statements, that is, 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
) to
Student St left
Join score s on st.stuid = S.S Tuid left
Join courses c on c.courseno = S.courseno
Group by St.stuid;

But the result is this.

Here I will not do more to repeat, presumably we also understand. So if it doesn't work, how do you do it?

Yes, it's going to be like a normal statement, make a declaration, stitch the statement together, and then execute, which is the following

SET @sql = NULL;
SELECT
 group_concat (DISTINCT
  CONCAT (
   ' MAX (IF (c.coursenm = ',
   c.coursenm, ', S.scores,
   0)) As ',
   c.coursenm, '
  ) into @sql from
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 get the following results.

Yes, just like all the statements that were spelled out at the beginning, this is the purpose of the dynamic row-column. And we don't have to know how many courses, and we don't need to list the names of these courses.

Of course, this sentence splicing query can add conditional query, such as we want to query number is 1003 of the results
That's the following.

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 @sql from
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;

In contrast to the preceding statement, we can see that after the left join in the second row I changed a bit, and that the previous variable added a @stuid [note: the @ symbol here is defined as a variable idiom in the SQL statement, which I personally understand should be used to differentiate it! ]

So the question is, row-and-column queries have been implemented, how to write a stored procedure in the title? Yes, that's right, that's the stored procedure!

Like the above statement, if we operate directly in MySQL is not a problem, but if used in the project, then this statement obviously we can not use, and I do the project is a combination of mybatis, we all know that in the MyBatis XML file in the write their own SQL statements, But it's obvious that we can't put it in the XML file.

And the most important thing is, here can not use if conditions, such as we have to determine whether the study number is empty or equal to 0 plus conditions for query, but this is not supported.
Yes, it's the following.

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 @sql from
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 then
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;

Yes, I just add if it is not supported by others, is so willful.

So we're going to use the stored procedure. And the advantage of using stored procedures is that it is convenient for us to call, the equivalent of a function, the other may be similar to the query does not need to repeat the code, directly adjust the stored procedure is good, but also arbitrary plus if conditions to judge, how beautiful things, haha ~.

So when it comes to stored procedures, how do we write this?
To create a statement for a stored procedure I don't have much to write about, here. Place the above query directly on the begin and end of the creation stored procedure, as follows:

DELIMITER && 
drop procedure if exists sp_querydata;
Create Procedure sp_querydata (in stuid varchar)
reads SQL DATA 
BEGIN

SET @sql = NULL;
SET @stuid = NULL;
SELECT
 group_concat DISTINCT
  CONCAT (
   ' MAX (IF (c.coursenm = ', C.coursenm, ', S.scores,
   ', 0 ) as ",
   c.coursenm, ' \") into @sql from
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 <> ' then
SET @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 to the simple SQL statement above, we can see that the IF statement is used here to judge the number.

But note here that the IF statement here is not like our usual Java ah that kind of writing is the following

if (condition)
{
Block of statements to execute
}
Yes, the IF statements in SQL are different, do not need parentheses or anything, just like speaking English directly

IF @stuid is not null and @stuid!= 0 then
SET @sql = CONCAT (@sql, ' where St.stuid = ', @stuid, ' ');

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

And then we can call this SP by passing parameters.

Call ' Sp_querydata ' (' 1001 ');

Get the following results

Of course, we can just pass through the empty strings.

Call ' Sp_querydata ' (');

Also get the results we want

Well, that's the way I do the dynamic row-column implementation in MySQL.

Summary and questions

At the beginning, I just wanted to do a row, write a sudden discovery to be dynamic, because I'm not sure how many columns.
Look for information on the Internet, but do not understand!

Later, the reference to the pivot table with the dynamic columns in MySQL this, just write out.

Then there are a variety of problems, first in the SQL statement to add if conditions, I like to write Java as usual, found that there is no use, online also said that is the

IF (Stuid is not null && stuid <> ") then
SET @stuid = stuid;
SET @sql = CONCAT (@sql, ' Where st.stuid = \ ', @stuid, ' \ ');
End IF;  

But I wrote this after no use, or an error, looking for a lot of later found that it is not so written, and then changed over.

After the change, I thought it was OK, but I still can't. Then I wondered if it wasn't possible to use if, because it wasn't a function or procedure, so I wrote the statement that created the procedure.

After the transformation, procedure was successfully created. After that, I'll try to see if I can, after calling procedure, when the results come out.

Well, this process is still a lot of gains, for MySQL row, and stored procedures, as well as in the SQL statement in the use of different places.
Moreover, this row of the implementation of the column, the project basically no big problem on the data processing, quite good ah, haha ~

That's all the stuff that I do in a row-and-column implementation, and relatively speaking, I think it's pretty clear here, so as long as you have the patience to read it and study it carefully, this content will be good for your row-and-column transfer.