Mysql脚本实现行转列 – 清风亦平凡

背景

某项目数据统计，为了更加直观显示一些数据，刚好有这个行转列的一个需求。行转列的需求其实在平常业务中也是比较常见的，在数据统计中使用的比较频繁。行转列以前也遇到过，之前数据库使用的是Microsoft SQL Server。目前使用的是MySQL数据库，这里也做一下简单的记录。

实现

结构与数据准备

为了更好的理解行转列，这里准备一张结构简单都表以及数据。


DROP TABLE IF EXISTS `project_completion_rate`;

CREATE TABLE `project_completion_rate` (
  `id` int(8) NOT NULL,
  `project_user` varchar(20) DEFAULT NULL,
  `project_content` varchar(20) DEFAULT NULL,
  `project_rate` int(8) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


insert  into `project_completion_rate`(`id`,`project_user`,`project_content`,`project_rate`) values 
(1,'张三','机器人设计',80),
(2,'张三','图像识别',70),
(3,'张三','虚拟化技术',90),
(4,'张三','CPU设计',60),
(5,'李四','机器人设计',95),
(6,'李四','图像识别',96),
(7,'李四','虚拟化技术',97),
(8,'李四','CPU设计',99),
(9,'王五','机器人设计',100),
(10,'王五','虚拟化技术',100);

原始数据示例展示

固定行转列

当需要行转列的项比较少且显示项数目无频繁变动的时候，可以使用以下方式实现行转列的需求。如果行转列的项变动频繁或者项比较多，此种方式实现行转列就不适合。



SELECT project_user AS '项目人员'
	    ,Max(CASE  WHEN p.project_content='CPU设计' THEN p.project_rate ELSE 0 END) AS  'CPU设计'
	    ,Max(CASE  WHEN p.project_content='图像识别' THEN p.project_rate ELSE 0 END) AS  '图像识别'
	    ,Max(CASE  WHEN p.project_content='机器人设计' THEN p.project_rate ELSE 0 END) AS  '机器人设计'
         ,Max(CASE  WHEN p.project_content='虚拟化技术' THEN p.project_rate ELSE 0 END) AS  '虚拟化技术'
FROM project_completion_rate p GROUP BY p.project_user;

动态行转列

动态行转列的实现方式，是为了解决行转列中的项变动频繁或者项比较多的时候而出现的。不固定的项的行转列就可以使用以下方式尝试完成。


-- 动态行转列脚本实现
SET @sql = NULL;
SELECT
 GROUP_CONCAT(DISTINCT
  CONCAT(
   'Max(IF(p.project_content = ''',
   p.project_content,
   ''', p.project_rate, 0)) AS ''',
   p.project_content, ''''
  )
 ) INTO @sql
FROM project_completion_rate p;
 
SET @sql = CONCAT('Select project_user AS ''项目人员'',', @sql, 
            ' From project_completion_rate p Group by p.project_user' );
						
PREPARE stmt FROM @sql;  -- 动态生成脚本
EXECUTE stmt;            -- 动态执行脚本
DEALLOCATE PREPARE stmt; -- 释放