MySQL rows converted to columns

1. Build a table

CREATE TABLE ' T_log_code_num ' (
' id ' int (one) not NULL auto_increment,
' server_id ' int (3) is not NULL,
' Date ' date is not NULL,
' Code ' varchar (not NULL),
' num ' int (ten) is not NULL,
PRIMARY KEY (' id ')
) Engine=myisam DEFAULT Charset=utf8;

2. Data entry

INSERT into T_log_code_num (' server_id ', ' Date ', ' Code ', ' num ') VALUES (' 1 ', ' 2017-07-31 ', ' 201058 ', ' 1 ');
INSERT into T_log_code_num (' server_id ', ' Date ', ' Code ', ' num ') VALUES (' 2 ', ' 2017-07-31 ', ' 201612 ', ' 2113 ');
INSERT into T_log_code_num (' server_id ', ' Date ', ' Code ', ' num ') VALUES (' 1 ', ' 2017-07-30 ', ' 201058 ', ' 1 ');
INSERT into T_log_code_num (' server_id ', ' Date ', ' Code ', ' num ') VALUES (' 2 ', ' 2017-07-30 ', ' 201314 ', ' 310 ');
INSERT into T_log_code_num (' server_id ', ' Date ', ' Code ', ' num ') VALUES (' 1 ', ' 2017-07-29 ', ' 201322 ', ' 1890 ');
INSERT into T_log_code_num (' server_id ', ' Date ', ' Code ', ' num ') VALUES (' 2 ', ' 2017-07-29 ', ' 201203 ', ' 379 ');

3. Data display

4. Statistical SQL

Select T. ' Code ',
SUM (case t.date if ' 2017-07-29 ' then t.num else 0 end) as ' 2017-07-29 ',
SUM (case t.date if ' 2017-07-30 ' then t.num else 0 end) as ' 2017-07-30 ',
SUM (case t.date if ' 2017-07-31 ' then t.num else 0 end) as ' 2017-07-31 '
From (
Select Date,code, SUM (num) num from T_log_code_num GROUP by Date,code
) T GROUP by T. ' Code ';

5. Statistical results

The code is as follows:

CREATE TABLE' T_log_code_num ' (' ID ')int( One) not NULLauto_increment, ' server_id 'int(3) not NULL, ' Date ' date not NULL, ' Code 'varchar( -) not NULL, ' num 'int(Ten) not NULL,  PRIMARY KEY(' id ')) ENGINE=MyISAMDEFAULTCHARSET=UTF8;INSERT  intoT_log_code_num (' server_id ', ' Date ', ' Code ', ' num ')VALUES('1','2017-07-31','201058','1');INSERT  intoT_log_code_num (' server_id ', ' Date ', ' Code ', ' num ')VALUES('2','2017-07-31','201612','2113');INSERT  intoT_log_code_num (' server_id ', ' Date ', ' Code ', ' num ')VALUES('1','2017-07-30','201058','1');INSERT  intoT_log_code_num (' server_id ', ' Date ', ' Code ', ' num ')VALUES('2','2017-07-30','201314','310');INSERT  intoT_log_code_num (' server_id ', ' Date ', ' Code ', ' num ')VALUES('1','2017-07-29','201322','1890');INSERT  intoT_log_code_num (' server_id ', ' Date ', ' Code ', ' num ')VALUES('2','2017-07-29','201203','379');SELECT *  fromT_log_code_num;SelectT. ' Code ',sum( CaseT.date when '2017-07-29'  ThenT.numElse 0 End) as '2017-07-29',    sum( CaseT.date when '2017-07-30'  ThenT.numElse 0 End) as '2017-07-30',    sum( CaseT.date when '2017-07-31'  ThenT.numElse 0 End) as '2017-07-31'  from (SelectDate,code,sum(num) num fromT_log_code_numGROUP  bydate,code) TGROUP  byT. ' Code ';

MySQL rows converted to columns