How to change the MySQL column to display data simple implementation

How to change the MySQL column to display data simple implementation

To create a test table:

   1:DROPTABLEIFEXISTS ' test ';

 2:  create  table  ' test ' (

 3:  ' year  ' 

 4:  ' month  ' 

 5:  ' Amount '  Double  default  null  

   6:DEFAULT Charset=utf8;

Insert data:

 1:  INSERT  into  ' test ' values  ( ' 1991 ' ,  ' 1 ' ,  ' 1.1 ' ); 

 2:  INSERT  into  ' test ' values  ( ' 1991 ' ,  ' 2 ' ,  ' 1.2 ' ); 

 3:  INSERT  into  ' test ' values  ( ' 1991 ' ,  ' 3 ' ,  ' 1.3 ' ); 

 4:  INSERT  into  ' test ' values  ( ' 1991 ' ,  ' 4 ' ,  ' 1.4 ' ); 

 5:  INSERT  into  ' test ' values  ( ' 1992 ' ,  ' 1 ' ,  ' 2.1 ' ); 

 6:  INSERT  into  ' test ' values  ( ' 1992 ' ,  ' 2 ' ,  ' 2.2 ' ); 

 7:  INSERT  into  ' test ' values  ( ' 1992 ' ,  ' 3 ' ,  ' 2.3 ' ); 

   8:intoVALUES (' 1992'4 '2.3');

When you see the topic, think carefully:

Use SUM (IF ()) to generate columns + with ROLLUP to generate total rows and use Ifnull to display the summary row headings as Total_num

Realize
The SQL code block is as follows:

 1:  select  year , 

 2:  sum
     (if  (month  =1,amount,0)) as  "M1", 

 3:  sum  (if  (month  =2,amount,0)) as  "M2", 

 4:  sum
     (if  (month  =3,amount,0)) as  "M3", 

 5:  sum  (if  (month  =4,amount,0)) as  "M4" 

 6:  from  Test 

   7:GROUPby year;

The effect is as follows:

How to change the MySQL column to display data simple implementation