mysql行转列与列转行

创建表 user 并插入测试数据.

CREATE TABLE `user`  (
  `id` int(11) NOT NULL,
  `field_number` int(11) NULL DEFAULT NULL,
  `field_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `field_value` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `field_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `model_num` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, 1, 'name', '张三', 'string', 10001);
INSERT INTO `user` VALUES (2, 1, 'age', '18', 'int', 10001);
INSERT INTO `user` VALUES (3, 2, 'name', '李四', 'string', 10001);
INSERT INTO `user` VALUES (4, 2, 'age', '18', 'int', 10001);

原数据

select *  from user;

行转列

SELECT
	`field_number` as  用户编号,
	MAX( CASE WHEN field_name = 'age' THEN field_value END )  as 'age',
	MAX( CASE WHEN field_name = 'name' THEN field_value END ) as 'name'
FROM
	user 
GROUP BY
	`field_number`;

列转行

SELECT
field_number,
GROUP_CONCAT(CASE WHEN field_name = 'name' THEN field_value END SEPARATOR '') 'name',
GROUP_CONCAT(CASE WHEN field_name = 'age' THEN field_value END SEPARATOR '') 'age'
FROM user
GROUP BY field_number;

动态列实现参考

http://jintang.zone/2021/08/31/动态字段存储方案对比.html