Mysql列转行与行转列

列转行

创建一张表incre_table，并且存储的id值尽可能大于字段里存的值个数，默认从1开始：

CREATE TABLE `incre_table`  (
  `AutoIncreID` int(11) NULL DEFAULT NULL
);
INSERT INTO `incre_table` VALUES (1);
INSERT INTO `incre_table` VALUES (2);
INSERT INTO `incre_table` VALUES (3);
INSERT INTO `incre_table` VALUES (4);
INSERT INTO `incre_table` VALUES (5);
INSERT INTO `incre_table` VALUES (6);
INSERT INTO `incre_table` VALUES (7);
INSERT INTO `incre_table` VALUES (8);
INSERT INTO `incre_table` VALUES (9);

SQL语句

select a.ID,substring_index(substring_index(a.cname,',',b.AutoIncreID),',',-1) as cname from test a 
join 
incre_table b on b.AutoIncreID <= (length(a.cname) - length(replace(a.cname,',',''))+1)

原始数据：

列转行结果：

行专列

SQL语句

select cname,GROUP_CONCAT(grade) 
from test 
group by cname

原数据：

行转列结果