Originality: We sometimes encounter this kind of requirement when we are working on a project.

hobby with name Zhang San needs to be merged as follows:

Of course, you can also merge hobby into one column through mysql's group_comcat. That's very simple. I won't introduce it here.

Build tables first, insert data

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_user
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(5) NULL DEFAULT NULL,
  `hobby` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of t_user
-- ----------------------------
INSERT INTO `t_user` VALUES (1, 'Zhang San', 18, 'read');
INSERT INTO `t_user` VALUES (2, 'Zhang San', 18, 'Play games');
INSERT INTO `t_user` VALUES (3, 'Zhang San', 18, 'Writing code');

Rewrite SQL

SELECT  
  GROUP_CONCAT(
    CONCAT(  
      '(select hobby from t_user where id = ',  
      id,  
      ') AS ',  
      CONCAT('hobby',id)
    )  
  ) INTO @sql  
FROM t_user;
SET @sql = CONCAT('select name,age, ', @sql, ' from t_user GROUP BY name');
PREPARE stmt FROM @sql;  
EXECUTE stmt;  
DEALLOCATE PREPARE stmt; 

The above is actually equivalent to

select name,age,(select hobby from t_user where id = 1) as hobby1,(select hobby from t_user where id = 2) as hobby2,
(select hobby from t_user where id = 3) as hobby3 from t_user GROUP BY name;

results of enforcement

Above is the case of Zhang Sanshi, the only name in the table.

And when there are multiple users in the table

Building tables and interpolating data

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_user
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(5) NULL DEFAULT NULL,
  `hobby` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of t_user
-- ----------------------------
INSERT INTO `t_user` VALUES (1, 'Zhang San', 18, 'read');
INSERT INTO `t_user` VALUES (2, 'Zhang San', 18, 'Play games');
INSERT INTO `t_user` VALUES (3, 'Zhang San', 18, 'Writing code');
INSERT INTO `t_user` VALUES (4, 'Wang Wu', 20, 'Having dinner');
INSERT INTO `t_user` VALUES (5, 'Wang Wu', 20, 'Sleep');
INSERT INTO `t_user` VALUES (6, 'Wang Wu', 20, 'Doudou');

Because if you want to query Wang Wu's hobby from 1, and Mysql does not have rownum like Oracle, you create rownum through sql

-- Set up rownum
SELECT @rownum:=@rownum+1 AS rownum, t_user.* 
FROM (SELECT @rownum:=0) r, t_user where name='Wang Wu';

Then query

set @name = 'Wang Wu';
SELECT  
  GROUP_CONCAT(DISTINCT 
    CONCAT(  
      '(select hobby from (SELECT @rownum:=@rownum+1 AS rownum, t_user.* 
FROM (SELECT @rownum:=0) r, t_user where name="',@name,'") t where t.rownum = ',  
      t.rownum,  
      ') AS ',  
      CONCAT('hobby',rownum)
    )  
  ) INTO @sql  
FROM (SELECT @rownum:=@rownum+1 AS rownum, t_user.* 
FROM (SELECT @rownum:=0) r, t_user where name=@name) t;
SET @sql = CONCAT('select distinct name,age, ', @sql, ' from t_user where name ="',@name,'"');
PREPARE stmt FROM @sql;  
EXECUTE stmt;  
DEALLOCATE PREPARE stmt; 

results of enforcement

Finish sharing

Here's how Mysql can be dynamically implemented by row-column transformation.https://blog.csdn.net/xeseo/article/details/24364921

How to dynamically generate PivotTable of Title under MYSQL.

First, use the following script to generate a table called "properties" that contains several attributes.

1. CREATE TABLE properties (  
2.   id INT(11) NOT NULL AUTO_INCREMENT,  
3.   item_id INT(11) DEFAULT NULL,  
4.   property_name VARCHAR(255) DEFAULT NULL,  
5.   value VARCHAR(255) DEFAULT NULL,  
6.   PRIMARY KEY (id)  
7. );  
8. INSERT INTO properties VALUES   
9.   (1, 1, 'color', 'blue'),  
10.   (2, 1, 'size', 'large'),  
11.   (3, 1, 'weight', 65),  
12.   (4, 2, 'color', 'orange'),  
13.   (5, 2, 'weight', 57),  
14.   (6, 2, 'size', 'large'),  
15.   (7, 3, 'size', 'small'),  
16.   (8, 3, 'color', 'red'),  
17.   (9, 3, 'weight', 12),  
18.   (10, 4, 'color', 'violet'),  
19.   (11, 4, 'size', 'medium'),  
20.   (12, 4, 'weight', 34),  
21.   (13, 5, 'color', 'green'),  
22.   (14, 5, 'weight', 10);  

As we all know, MySql has no automatic table conversion function. Of course, we can use some additional programs or tools to connect MySQL to perform data conversion. But here, we will discuss how to write a query manually to achieve data conversion. The query can be implemented as follows:

1. SELECT  
2.   item_id,  
3.   MAX(case when property_name = 'color' then VALUE else NULL END) AS color,  
4.   MAX(CASE property_name WHEN 'weight' THEN value ELSE null END) AS weight,  
5.   MAX(IF(property_name = 'size', value, NULL)) AS size,  
6.   ...  
7.   ...  
8. FROM  
9.   properties  
10. GROUP BY  
11.   item_id;  

The implementation of dynamically generating queries is as follows:

1. SET @sql = NULL;  
2. SELECT  
3.   GROUP_CONCAT(DISTINCT  
4.     CONCAT(  
5.       'MAX(IF(property_name = ''',  
6.       property_name,  
7.       ''', value, NULL)) AS ',  
8.       property_name  
9.     )  
10.   ) INTO @sql  
11. FROM properties;  
12. SET @sql = CONCAT('SELECT item_id, ', @sql, ' FROM properties GROUP BY item_id');  

1. SELECT  
2.   item_id,  
3.   MAX(IF(property_name = 'color', value, NULL)) AS color,  
4.   MAX(IF(property_name = 'size', value, NULL)) AS size,  
5.   MAX(IF(property_name = 'weight', value, NULL)) AS weight  
6. FROM  
7.   properties  
8. GROUP BY  
9.   item_id  

MySQL uses system variables for GROUP_CONCAT results group_concat_max_len The default value is 1024. So, if your table has many columns, it's better to set the value higher.

1. SET @@group_concat_max_len = 5000;  
2. SELECT GROUP_CONCAT(column_name) FROM table;  

1. PREPARE stmt FROM @sql;  
2. EXECUTE stmt;  
3. DEALLOCATE PREPARE stmt;  

2. +---------+--------+--------+--------+
3. | item_id | color | size | weight |
4. +---------+--------+--------+--------+
5. | 1 | blue | large | 65 |
6. | 2 | orange | large | 57 |
7. | 3 | red | small | 12 |
8. | 4 | violet | medium | 34 |
9. | 5 | green | NULL | 10 |
10. +---------+--------+--------+--------+