原创 : 我们在做项目中有时候会遇到这种需求

需要将name 为张三的hobby合并为如下: 

当然你也可以通过mysql的 group_comcat将hobby合并成一列,那个很简单,这里不做介绍

先建表,插入数据

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, '张三', 18, '读书');
INSERT INTO `t_user` VALUES (2, '张三', 18, '打游戏');
INSERT INTO `t_user` VALUES (3, '张三', 18, '写代码');

再编写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; 

上面的写法其实相当于

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;

执行结果

以上是表中只有名为张三时的情况

还有当表中存在多个用户时

建表,插数据

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, '张三', 18, '读书');
INSERT INTO `t_user` VALUES (2, '张三', 18, '打游戏');
INSERT INTO `t_user` VALUES (3, '张三', 18, '写代码');
INSERT INTO `t_user` VALUES (4, '王五', 20, '吃饭');
INSERT INTO `t_user` VALUES (5, '王五', 20, '睡觉');
INSERT INTO `t_user` VALUES (6, '王五', 20, '打豆豆');

因为如果要查询王五的hobby要从1开始,而Mysql不像Oracle有rownum,所以通过sql创建rownum

-- 设置rownum
SELECT @rownum:=@rownum+1 AS rownum, t_user.* 
FROM (SELECT @rownum:=0) r, t_user where name='王五';

然后再查询

set @name = '王五';
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; 

执行结果

分享完毕

以下为转载:  Mysql 如何通过行列转化动态实现   https://blog.csdn.net/xeseo/article/details/24364921

MYSQL下如何动态生成标题的数据透视表。

先用以下script生成一张包含若干属性的表叫"properties"。

众所周知，MySql里并没有自动表转换的功能。当然，我们可以用一些额外的程序或工具来连接MySQL去执行数据转换。但在这里，我们来探讨下如何手动的写一个查询来实现数据转换。该查询可以这样实现：

动态生成查询的实现如下：

MySQL对于GROUP_CONCAT结果用系统变量group_concat_max_len做了限制，默认值是1024。所以，如果你的表有很多列，最好将该值设大一些。

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. +---------+--------+--------+--------+