mysql - msyql纵表转化横表问题

Question

构造表sql语句：

DROP TABLE IF EXISTS `hr_new_sign_activities_field_data`;
CREATE TABLE `hr_new_sign_activities_field_data` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `field_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '字段ID',
  `value` varchar(255) DEFAULT NULL COMMENT '提交过来的数据',
  `acitvity_info_id` int(11) NOT NULL DEFAULT '0' COMMENT '表hr_new_sign_activities_info的自增ID',
  PRIMARY KEY (`id`),
  KEY `acitvity_info_id` (`acitvity_info_id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8 COMMENT='用户提交的表单数据----这个主要是存储的后台设置的字段，用户提交的字段数据';

-- ----------------------------
-- Records of hr_new_sign_activities_field_data
-- ----------------------------

INSERT INTO `hr_new_sign_activities_field_data` VALUES ('6', '38', '我的企业', '5');
INSERT INTO `hr_new_sign_activities_field_data` VALUES ('7', '39', '13985623562', '5');
INSERT INTO `hr_new_sign_activities_field_data` VALUES ('8', '40', '13985623562@qq.com', '5');
INSERT INTO `hr_new_sign_activities_field_data` VALUES ('15', '38', '我的企业哈', '8');
INSERT INTO `hr_new_sign_activities_field_data` VALUES ('16', '39', '13652356524', '8');
INSERT INTO `hr_new_sign_activities_field_data` VALUES ('17', '40', 'wangjian@shixiba.com', '8');
INSERT INTO `hr_new_sign_activities_field_data` VALUES ('18', '38', 'sasassasasa', '9');
INSERT INTO `hr_new_sign_activities_field_data` VALUES ('19', '39', '13985236523', '9');
INSERT INTO `hr_new_sign_activities_field_data` VALUES ('20', '40', '13985236523@qq.com', '9');
INSERT INTO `hr_new_sign_activities_field_data` VALUES ('24', '38', '我速度1', '11');
INSERT INTO `hr_new_sign_activities_field_data` VALUES ('25', '39', '13562354125', '11');
INSERT INTO `hr_new_sign_activities_field_data` VALUES ('26', '40', '1253652361@qq.com', '11');

我现在的数据表是这样的

我现在期望转化为
目前我的sql语句是这样的

select
 acitvity_info_id ,
 CASE value when '38'
 then value else '' end as field_38,
 CASE value when '39'
 then value else '' end as field_39 ,
 CASE value when '40'
 then value else '' end as field_40

   from  hr_new_sign_activities_field_data  where acitvity_info_id in (5,8,9 , 11)  GROUP BY acitvity_info_id

sql语句的效果如下：

我期望的最终效果如下：

Answer 1

已经找到解决办法了

select  m.acitvity_info_id ,
CASE m.value when '38'  then m.value 
else (select value from hr_new_sign_activities_field_data where acitvity_info_id =m.acitvity_info_id and field_id=38) end as field_38, 
CASE value when '39'  then value else (select value from hr_new_sign_activities_field_data where acitvity_info_id =m.acitvity_info_id and field_id=39) end as field_39 , 
CASE value when '40'  then value else (select value from hr_new_sign_activities_field_data where acitvity_info_id =m.acitvity_info_id and field_id=40) end as field_40    
from  hr_new_sign_activities_field_data m  where acitvity_info_id in (5,8,9 , 11)  GROUP BY acitvity_info_id