sql语句中行转列总结

一.设计表结构

     

二.填充数据

     

三.结果类型

    

四.实现

     1.直接在整张表中进行刷选

      SELECT YEAR ,SUM(CASE WHEN QUARTER='m1' THEN number ELSE 0 END)  AS m1,SUM(CASE WHEN QUARTER='m2' THEN number ELSE 0 END) AS m2 FROM test
GROUP BY YEAR

     2.进行两表拼接

      SELECT r1.year,r1.m3 AS m1,r2.m4 AS m2 FROM 
(SELECT YEAR, SUM(number) AS  m3 FROM test WHERE QUARTER='m1' GROUP BY YEAR) r1,
(SELECT YEAR, SUM(number) AS  m4 FROM test  WHERE QUARTER='m2' GROUP BY YEAR) r2
WHERE  r1.year=r2.year