mysql行转列的处理（存储过程+预制语句+动态SQL）

zhangzhufu12

个人总结的关于mysql行转列中动态拼接SQL语句的实例（无参数）拿来与大家分享：

BEGIN
/*测试行转列 测试表 t2,测试数据：*/
/*
转载请注明出处，谢谢！
创建人：zhangzhufu 2014-12-4 14:10:54
*/
/*
伪代码：
1.从表中获取动态列名和动态列值
2.使用group_concat函数组合列名,组合列值
3.使用函数统计列名个数
4.使用函数拆分列名用于拼接创建临时表sql语句
5.使用函数拆分列值用于拼接向临时表插入数据语句
6.遍历临时表
备注：1.预制语句必须用用户变量来接收
           2.预制语句必须满足标准sql语法，非数字类型变量必须加单双引号，区别使用
           3.预制语句必须为单一sql语句
           4.预制语句中包含不定列的sql子句必须事先将其设置为空字符串，不可默认使用null
*/
DECLARE c                 VARCHAR(255)    DEFAULT NULL;  -- 接收课程名称
DECLARE s                 VARCHAR(255)    DEFAULT NULL;  -- 接收成绩
DECLARE k                 INT                        DEFAULT 0;          -- 列名称数量
DECLARE varg            INT                        DEFAULT 1;          -- 变量g
DECLARE sql2             VARCHAR(255)   DEFAULT '';           -- 动态sql子句

#获取列名列值字符串
SELECT GROUP_CONCAT(crouse),GROUP_CONCAT(scord) INTO c,s FROM t2 WHERE 1 = 1;
#获取列个数
SET k = (length(c)-length(replace(c,',',''))+1);

#循环拼接动态sql语句
WHILE varg <= k DO
        # 拼写SQL子句，返回结果为一行记录
        SET sql2 = CONCAT(sql2,',',mysql_splitString(s,varg),' ','as',' ',mysql_splitString(c,varg));
        SET varg = varg+1; -- 循环条件
END WHILE;
#消除第一个逗号
SET sql2 = SUBSTR(sql2,2);
#拼接动态sql总句
SET @sql1 = CONCAT('CREATE TEMPORARY TABLE pkd select ', sql2);  

#预执行动态SQL语句
PREPARE rowtocolumn FROM @sql1 ;
#预览sql1 = CREATE TEMPORARY TABLE pkd select 66 as 语文,77 as 数学,88 as 英语,99 as 物理;
#执行动态sql
EXECUTE rowtocolumn;
#遍历临时表
SELECT * FROM pkd;
#删除临时表
DROP TABLE pkd;
#解除预处理
DEALLOCATE PREPARE rowtocolumn;

#SELECT sql1;

END


注：表结构图片添加有误，如有需要请查看原地址
引用地址：http://zhangzhufu12.blog.163.com/blog/static/1634120252014116105734264/