Mysql行转列，列转行----存储过程实现

1.行转列存储过程实现方式
准备数据：
CREATE TABLE studentscores (
username varchar(20) COLLATE utf8_bin DEFAULT NULL,
subjectname varchar(30) COLLATE utf8_bin DEFAULT NULL,
score double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC;
INSERT INTO crm.studentscores(username, subjectname, score) VALUES (‘张三’, ‘语文’, 80);
INSERT INTO crm.studentscores(username, subjectname, score) VALUES (‘张三’, ‘数学’, 90);
INSERT INTO crm.studentscores(username, subjectname, score) VALUES (‘张三’, ‘英语’, 70);
INSERT INTO crm.studentscores(username, subjectname, score) VALUES (‘张三’, ‘生物’, 85);
INSERT INTO crm.studentscores(username, subjectname, score) VALUES (‘李四’, ‘语文’, 80);
INSERT INTO crm.studentscores(username, subjectname, score) VALUES (‘李四’, ‘数学’, 92);
INSERT INTO crm.studentscores(username, subjectname, score) VALUES (‘李四’, ‘英语’, 76);
INSERT INTO crm.studentscores(username, subjectname, score) VALUES (‘李四’, ‘生物’, 88);
INSERT INTO crm.studentscores(username, subjectname, score) VALUES (‘码农’, ‘语文’, 60);
INSERT INTO crm.studentscores(username, subjectname, score) VALUES (‘码农’, ‘数学’, 82);
INSERT INTO crm.studentscores(username, subjectname, score) VALUES (‘码农’, ‘英语’, 96);
INSERT INTO crm.studentscores(username, subjectname, score) VALUES (‘码农’, ‘生物’, 78);
得到如下结果集：

通过存储过程实现行转列：

CREATE PROCEDURE test1()
BEGIN
	#课程名称
  DECLARE cname_n VARCHAR (20) ; #所有课程数量
	DECLARE count INT ; #计数器
	DECLARE i INT DEFAULT 0 ; #拼接SQL字符串
			SET @s = 'SELECT username' ;
			SET count = (          #计算总课程数量
				SELECT
					COUNT(DISTINCT subjectname)
				FROM
					studentscores
			) ;
			WHILE i < count DO
			SET cname_n = (
				SELECT
					subjectname
				FROM
					studentscores
				GROUP BY subjectname 
				LIMIT i,1
			) ;
			SET @s = CONCAT(
				@s,
				', SUM(CASE subjectname WHEN ',
				'\'',
				cname_n,
				'\'',
				' THEN score ELSE 0 END)',
				' AS ',
				'\'',
				cname_n,
				'\''
			) ;
			SET i = i + 1 ;
			END
			WHILE ;
			SET @s = CONCAT(
				@s,
				' FROM studentscores GROUP BY username'
			) ; #用于调试
			#SELECT @s;
			PREPARE stmt
			FROM
				@s ;
			EXECUTE stmt ;
			END
CALL test1();
#删除存储过程
DROP PROCEDURE test1;

参考博客：
https://blog.****.net/u013938484/article/details/50552747
https://blog.****.net/testcs_dn/article/details/49847299