列转行
创建一张表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
原数据:
行转列结果