Mysql 列传行

数据库user表数据

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `key` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `value` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('kkl', 'name', 'kkl');
INSERT INTO `user` VALUES ('hsl', 'age', '12');
INSERT INTO `user` VALUES ('hsl', 'sex', 'boy');
INSERT INTO `user` VALUES ('hsl', 'name', 'hsl');
INSERT INTO `user` VALUES ('kkl', 'age', '27');
INSERT INTO `user` VALUES ('kkl', 'sex', 'boy');

SET FOREIGN_KEY_CHECKS = 1;

执行的SQL

-- SELECT username,group_concat(`key`) AS `keys`,group_concat(`value`) AS `values`  FROM `user` GROUP BY username
-- SELECT DISTINCT `key` FROM `user`
-- SELECT DISTINCT
-- 	username,
-- 	( CASE WHEN `key` = "name" THEN `value` ELSE NULL END ) `name`,
-- 	( CASE WHEN `key` = "age" THEN `value` ELSE NULL END ) `age`,
-- 	( CASE WHEN `key` = "sex" THEN `value` ELSE NULL END ) `sex` 
-- FROM
-- 	`user`
SELECT a.username,GROUP_CONCAT(a.`name`) AS `name`,
GROUP_CONCAT(a.`sex`) AS `sex`,GROUP_CONCAT(a.`age`) AS `age` FROM ( SELECT DISTINCT
	username,
	( CASE WHEN `key` = "name" THEN `value` ELSE NULL END ) `name`,
	( CASE WHEN `key` = "age" THEN `value` ELSE NULL END ) `age`,
	( CASE WHEN `key` = "sex" THEN `value` ELSE NULL END ) `sex` 
FROM
	`user` ) a GROUP BY a.`username`

结果图