MySQL查询结果行转列方法

比如有SQL查询结果如下

想要转换为下表格：

两种方法

1、静态行转列

使用SUM、CASE

SUM(CASE field WHEN '固定值' THEN 0 ELSE 0 END ) '固定值'

例如：

SELECT
	`basiclaw_code` AS '基本法',
	`agent_grade` AS '职级',
	SUM(CASE `wage_name` WHEN '个人FYC' THEN 0 ELSE 0 END ) '个人FYC',
	SUM(CASE `wage_name` WHEN '个人RYC' THEN 0 ELSE 0 END ) '个人RYC',
	SUM(CASE `wage_name` WHEN '直辖团队FYC' THEN 0 ELSE 0 END ) '直辖团队FYC',
	SUM(CASE `wage_name` WHEN '所辖团队FYC' THEN 0 ELSE 0 END ) '所辖团队FYC',
	SUM(CASE `wage_name` WHEN '初年度佣金' THEN 0 ELSE 0 END ) '初年度佣金',
	SUM(CASE `wage_name` WHEN '续年度佣金' THEN 0 ELSE 0 END ) '续年度佣金',
	SUM(CASE `wage_name` WHEN '月度训练津贴' THEN 0 ELSE 0 END ) '月度训练津贴'
FROM
	`agent_cbs`.`wage_index_accessory` 
WHERE
	`basiclaw_code` IN ('DWQJBF-NEW')
	AND `del_flag` = '0' 
	-- AND `agent_grade` = 'GHS' 
ORDER BY
	`basiclaw_code` ASC,
	`agent_grade` ASC,
	`cal_level` ASC,
	`wage_order` ASC;

2、动态行转列

方法1 静态行转列 有个缺点就是如果需要转换的行值比较多，需要一个个去写，很麻烦且易出错。

可以使用MySQL内置的拼接函数来实现。

SELECT
	GROUP_CONCAT( DISTINCT CONCAT( 'SUM(IF(wage_name = ''', wage_name, ''', 0, 0)) AS ''', wage_name, '''' ) ) 
FROM
	wage_index_accessory 
WHERE 
	`basiclaw_code` IN ( 'DWQJBF-NEW' ) AND 
	`agent_grade` = 'GHS' AND 
	`del_flag` = '0' ;

拼接语句的查询效果如下（为了方便查看已做换行处理）：

SUM(IF(wage_name = '个人FYC', 0, 0)) AS '个人FYC',
SUM(IF(wage_name = '个人续年度服务报酬', 0, 0)) AS '个人续年度服务报酬',
SUM(IF(wage_name = '个人标保', 0, 0)) AS '个人标保',
SUM(IF(wage_name = '直辖组FYC', 0, 0)) AS '直辖组FYC',
SUM(IF(wage_name = '直辖组标保', 0, 0)) AS '直辖组标保',
SUM(IF(wage_name = '所辖组FYC', 0, 0)) AS '所辖组FYC',
SUM(IF(wage_name = '直辖部FYC', 0, 0)) AS '直辖部FYC',
SUM(IF(wage_na

这里有一个细节需要注意，仔细观察可以发现上面这一段查询结果是不完整的，为何呢？因为超出了MySQL默认的拼接列值字符串长度1024，所以会自动截断，只保留前面1024个字符。

这个问题，两种解决方法；

1. 设置group_concat_max_len的临时最大长度值，当前查询界面有效。

SET SESSION group_concat_max_len = 102400;

2. 修改MySQL的配置文件group_concat_max_len = 102400，然后重启服务

第2种方法比较麻烦，再者很多时候也不一定有这个权限，这里就用第一种方法。

设置后查看一下设置后的长度，长度变化为102400说明可以了。

查询拼接列值字符串长度
SHOW VARIABLES LIKE 'group_concat_max_len';

此时在执行拼接SQL语句，就不会被截断了。

最终还需要把拼接后的SQL语句封装一下，存入存储过程（存储函数只能返回一个值所以不行）中，来调用就可以实现动态的行转列。

完整的存储过程如下：

CREATE DEFINER=`root`@`%` PROCEDURE `generate_wage_export`()
    COMMENT '查询结果行转列demo'
BEGIN

    SET @sql = NULL;
		SELECT
			GROUP_CONCAT( DISTINCT CONCAT( 'SUM(IF(wage_name = ''', wage_name, ''', 0, 0)) AS ''', wage_name, '''' ) ) INTO @sql
		FROM
			wage_index_accessory 
		WHERE 
			`basiclaw_code` = 'DWQJBF-NEW' AND 
			`agent_grade` = 'GHS' AND 
			`del_flag` = '0';

		SET @sql = CONCAT(
			"SELECT `basiclaw_code` AS '基本法编码', ", 
			@sql, 
			" FROM `wage_index_accessory` WHERE `basiclaw_code`= 'DWQJBF-NEW' AND `agent_grade` = 'GHS' AND `del_flag` = '0'"
		);

		PREPARE stmt FROM @sql;
		EXECUTE stmt;
		DEALLOCATE PREPARE stmt;
	
END

调用存储过程：

// 调用这里记得也要设置下拼接列值字符串长度
SET SESSION group_concat_max_len = 102400;

CALL generate_wage_export();

至此得到想要的结果了