如何在oracle中进行列行转换
2个回答
展开全部
数据表示例:
假设要将name值作为行,course值作为列,转换后效果为:
对应的SQL如下:
方法1:使用表连接
SELECT DISTINCT a.name,
(SELECT score FROM grade b WHEREa.name=b.name AND b.course='语文') AS '语文',
(SELECT score FROM grade b WHEREa.name=b.name AND b.course='数学') AS '数学',
(SELECT score FROM grade b WHEREa.name=b.name AND b.course='英语') AS '英语'
FROM grade a
方法2:使用分组
SELECT name,
SUM(CASE course WHEN '语文' THEN score END) AS '语文',
SUM(CASE course WHEN '数学' THEN score END) AS '数学',
SUM(CASE course WHEN '英语' THEN score END) AS '英语'
FROM grade GROUP BY name
推荐于2018-03-13
展开全部
select
Name,
sum(decode(Subject,'语文',上半年成绩,0)) 语文,
sum(decode(Subject,'数学',上半年成绩,0)) 数学,
sum(decode(Subject,'英语',上半年成绩,0)) 英语,
sum(decode(Subject,'语文',下半年成绩,0)) 语文,
sum(decode(Subject,'数学',下半年成绩,0)) 数学,
sum(decode(Subject,'英语',下半年成绩,0)) 英语
from score
group by Name;
Name,
sum(decode(Subject,'语文',上半年成绩,0)) 语文,
sum(decode(Subject,'数学',上半年成绩,0)) 数学,
sum(decode(Subject,'英语',上半年成绩,0)) 英语,
sum(decode(Subject,'语文',下半年成绩,0)) 语文,
sum(decode(Subject,'数学',下半年成绩,0)) 数学,
sum(decode(Subject,'英语',下半年成绩,0)) 英语
from score
group by Name;
本回答被网友采纳
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询