行转列是什么
行转列就是把某一个字段的值作为唯一值,然后另外一个字段的行值转换成它的列值
----------------------------------------案列-----------------------------------
Oracle行转列
我们把成绩转换一下
第一种方法:分组
利用group by对学生进行分组,然后利用decode对对应的课程的成绩值进行转换,然后再求和即可得到结果值
sql案列:
select c.stuname,
sum(decode(b.coursename, ‘英语(2018上学期)’, t.score, 0)) as “英语(2018上学期)”,
sum(decode(b.coursename, ‘数学(2018上学期)’, t.score, 0)) as “英语(2018上学期)”,
sum(decode(b.coursename, ‘语文(2018上学期)’, t.score, 0)) as “英语(2018上学期)”
from STUDENT.SCORE t, student.course b, student.stuinfo c
where t.courseid = b.courseid and t.stuid = c.stuid
group by c.stuname
第二种方法:PIVOT() 函数
先看语法
SELECT * FROM (数据查询集)
PIVOT
(
SUM(Score/行转列后 列的值/) FOR
coursename/需要行转列的列/ IN (转换后列的值)
)
sql案列:
select * from (select c.stuname,
b.coursename,
t.score
from STUDENT.SCORE t, student.course b, student.stuinfo c
where t.courseid = b.courseid
and t.stuid = c.stuid )
PIVOT
(
SUM(score)
FOR coursename IN (‘英语(2018上学期)’ as 英语,‘数学(2018上学期)’ as 数学,‘语文(2018上学期)’ as 语文 )
) ;
效果展示:
Mysql行转列
mysql没有行转列函数,我只知道第一种通过分组的方式进行转换
----------------------------------------案列-----------------------------------
根据需求利用SUM或者MAX等函数,得到水果和蔬菜的数据进行转换,记得分组分组分组
SELECT
placeOrigin,
SUM( CASE WHEN alias = ‘水果’ THEN prodWgt ELSE 0 END ) AS “水果”,
SUM( CASE WHEN alias = ‘蔬菜’ THEN prodWgt ELSE 0 END ) AS “蔬菜”
FROM 表名
GROUP BY placeOrigin
效果展示: