mysql 列转行，合并字段（行转列）

数据表：

列转行：利用max(case when then)

合并字段显示:利用graup_cancat(name1,”;”,”name2”)

-- SELECT * from 20160413CaiDi WHERE 1=1 limit 0,5;

-- 列转行
select year,
 (CASE `month` WHEN 1 THEN money ELSE 0 END ) as m1,
 (CASE `month` WHEN 2 THEN money ELSE 0 END ) as m2,
 (CASE `month` WHEN 3 THEN money ELSE 0 END ) as m3,
 (CASE `month` WHEN 4 THEN money ELSE 0 END ) as m4 
from 20160413CaiDi;
 GROUP BY year;

-- 列转行
select year,
 (CASE `month` WHEN 1 THEN money ELSE 0 END ) as m1,
 (CASE `month` WHEN 2 THEN money ELSE 0 END ) as m2,
 (CASE `month` WHEN 3 THEN money ELSE 0 END ) as m3,
 (CASE `month` WHEN 4 THEN money ELSE 0 END ) as m4 
from 20160413CaiDi;
 GROUP BY year;

select year,
 MAX(CASE `month` WHEN 1 THEN money ELSE 0 END ) as m1,
 MAX(CASE `month` WHEN 2 THEN money ELSE 0 END ) as m2,
 MAX(CASE `month` WHEN 3 THEN money ELSE 0 END ) as m3,
 MAX(CASE `month` WHEN 4 THEN money ELSE 0 END ) as m4 
from 20160413CaiDi 
 GROUP BY year;

-- 列转行
select year,
 MAX(CASE `month` WHEN "1" THEN money ELSE null END ) as m1,
 MAX(CASE `month` WHEN "2" THEN money ELSE null END ) as m2,
 MAX(CASE `month` WHEN "3" THEN money ELSE null END ) as m3,
 MAX(CASE `month` WHEN "4" THEN money ELSE null END ) as m4 
from 20160413CaiDi 
 GROUP BY year;

-- 行专列（合并字段）

select year,GROUP_CONCAT(month,":",money) as appendWord
from 20160413CaiDi GROUP BY year,month;

select year,month,GROUP_CONCAT("money is:",money) as appendWord
from 20160413CaiDi GROUP BY year;