mysql dynamically get columns

1. The main reason is that all situations need to be taken into consideration when using the wording of case when, but in some cases you don't know what all the situations are, that is, the situation is uncertain.

2. First come to the sql result

SET @EE='';

SELECT 
    @EE:=CONCAT(@EE,'SUM(IF(type_string=\'',type_string,'\'',',amount_int,0)) AS ',type_string,',') 
FROM 
    (SELECT DISTINCT type_string FROM test) A;
    
SET @QQ=CONCAT('SELECT left(test.time_date,7) AS month,',LEFT(@EE,LENGTH(@EE)-1),' FROM mytest GROUP BY left(test.time_date,7)');

PREPARE stmt FROM @QQ;
execute stmt;
deallocate prepare stmt;

Note that I intercepted test_time, test_time is in the format of 2020-01-01, for example, my requirement is to count the data of each month, so I intercepted the characters such as 2020-01.
Note the replacement, the above type_string is the name, test_time is the date, and amount_int is the value

3. Demand

According to the monthly statistics of the data of each type, the first column in the result set is the time (yyyy-mm), and the second column is the sum of the numbers corresponding to each name.
As shown below

Insert picture description here

4. Ideas

The general idea is to group by name and date, as follows

select left(time_date,7) , type_string , sum(amount_int) from test GROUP BY left(time_date,7) , type_string

But the effect of writing like this is like this

Insert picture description here

Or use case when

select 
    left(test.time_date,7) as month,
	sum(case when type_string='card' then amount_int else 0 end) as card,
	sum(case when type_string='cash' then amount_int else 0 end) as cash,
	sum(case when type_string='cheque' then amount_int else 0 end) as cheque,
	sum(case when type_string='zhifubao' then amount_int else 0 end) as zhifubao
from
    test
GROUP BY
    left(test.time_date,7)

The result is as follows

Insert picture description here

Here is the need to use case when to write all type_string, if you don’t know how many types of type_string will be in the future? That is, what if type_string will increase in the future? This writing has limitations.

5. Attach a table statement

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test`  (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `emp_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '待补充' COMMENT '员工name',
  `emp_cost` int(10) NULL DEFAULT NULL COMMENT '消费',
  `score_int` int(10) NULL DEFAULT NULL,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `time_date` date NULL DEFAULT NULL,
  `time1_string` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `type_string` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `amount_int` int(20) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `test` VALUES (1, 'joe', 10, 10, 'a', '2021-04-23', '96.02', 'card', 10);
INSERT INTO `test` VALUES (2, 'sam', 20, 20, 'a', '2021-04-09', '110.25', 'card', 10);
INSERT INTO `test` VALUES (3, '待补充', NULL, 10, 'a', '2021-04-14', '80.00', 'cash', 20);
INSERT INTO `test` VALUES (4, '待补充', NULL, 5, 'a', '2021-01-01', '120.36', 'cheque', 20);
INSERT INTO `test` VALUES (5, '待补充', NULL, 6, 'a', '2021-02-01', '75.00', 'cash', 10);
INSERT INTO `test` VALUES (6, '待补充', NULL, 7, 'a', '2021-03-01', '71.25', 'card', 20);
INSERT INTO `test` VALUES (7, '待补充', NULL, 9, 'b', '2021-01-01', '85.63', 'card', 10);
INSERT INTO `test` VALUES (8, '待补充', NULL, 20, 'b', '2021-02-01', '86', 'cash', 20);
INSERT INTO `test` VALUES (9, '待补充', NULL, 15, 'b', '2021-03-01', '86.25', 'cash', 10);
INSERT INTO `test` VALUES (10, '待补充', NULL, 16, 'b', '2021-04-01', '99.99', 'cash', 20);
INSERT INTO `test` VALUES (11, '待补充', NULL, NULL, NULL, '2021-01-01', '', 'zhifubao', 12);