mysql数据库如何实现行列转换
1. 行转列：
方案一：
select name,
sum(case when course='java' then grade end) as java,
sum(case when course='C++' then grade end) as C++,
sum(case when course='C#' then grade end) as C#
from test group by name
方案二：
select distinct c.`name` AS name,
(select grade from test where name = c.`name` and course = 'java' )as java,
(select grade from test where name = c.`name` and course = 'C++' )as C++,
(select grade from test where name = c.`name` and course = 'C#' )as C#
from test c
结合项目用到的sql：
select MAIN_STATION_CODE_ as stationCode,
case when SUBSTR(PLAN_START_DATE_, 1, 10)=curdate() then STATION_ else "" end as firstDay,
case when SUBSTR(PLAN_START_DATE_, 1, 10)=DATE_SUB(curdate(),INTERVAL -1 DAY) then STATION_ else "" end as secondDay,
case when SUBSTR(PLAN_START_DATE_, 1, 10)=DATE_SUB(curdate(),INTERVAL -2 DAY) then STATION_ else "" end as thirdDay,
case when SUBSTR(PLAN_START_DATE_, 1, 10)=DATE_SUB(curdate(),INTERVAL -3 DAY) then STATION_ else "" end as fourthDay
from ps_overhaul_plan_row group by MAIN_STATION_CODE_
方案二拓展：
上面我们是采用逐个判断并拼接的方式来进行，那如果极端情况下，假如有成百上千个值需要判断怎么办？这种情况下，能够立即想到的是采用函数来拼接出对应的语句块，再合并在一起执行。
假如我们需要拼接下面一句：
(select GRADE_ FROM grade where name_ = c.name_ and course_ = ‘Math’) as Math，
使用concat函数（例子）：
select c.name_,concat('(SELECT grade_ from grade where name_=c.name_ ', 'and course_ =''', c.course_, '''', ') as ',c.course_, ')') from grade c
然后再次使用group_concat函数将多行转为一行：
SELECT GROUP_CONCAT(distinct concat('(SELECT grade_ from grade where name_=c.name_ ', 'and course_ =''', c.course_, '''', ') as ',c.course_, ')')) from grade c
最后再使用存储过程完成动态sql执行。
方案三：带汇总
select ifnull(uid,'Total') uid, uname,
sum(if(`course`='java',grade,0)) 'java',
sum(if(`course`='C++',grade,0)) 'C++',
sum(if(`course`='C#',grade,0)) 'C#',
sum(score) 'total'
from course
group by uid
with ROLLUP
方案四：使用group_concat函数
SELECT
id,
name,
group_concat(CASE WHEN subject = 'Math' THEN score END SEPARATOR '') '数学',
group_concat(CASE WHEN subject = 'English' THEN score END SEPARATOR '') '英语'
FROM test1
GROUP BY name;
2. 列转行
原表：
实现SQL：
SELECT d.name_,'Math' AS subject,d.Math_ AS score FROM
grade_column d
UNION ALL
SELECT d.name_, 'English' as SUBJECT,d.English_ as score from
grade_column d
列转行效果如下：
说明：SELECT “hello” as subject from dual;作用是新添加列subject，并且列值为hello；
总结：
行转列原理：CASE WHEN或IF,这两种都是判断条件，满足条件的时候我们把它当做新的一列。
列转行原理：UNION或UNION ALL,这两个都是把结果集合并起来，每次查询学生名称(基本列)和学科的其中一列的值,再把它们组合起来，这样结果集就只有学生名称和科目成绩两列了，这里多加了一列科目。