SQL列转换行的两种方法
时间：2021-07-01 10:21:17
帮助过：57人阅读
通过MAX...CASE...WHEN...转换列数据
select Student
as ‘姓名‘,
MAX(
CASE Subject
WHEN ‘语文‘ THEN Score
ELSE 0 END )
AS ‘语文‘,
--替换Subject列数据为Score列数据
MAX(
CASE Subject
WHEN ‘英语‘ THEN Score
ELSE 0 END )
AS ‘英语‘ --替换Subject列数据为Score列数据
from Scores
group by Student
order by Student
SELECT
Student as ‘姓名‘,
--通过AVG函数转换语文列
AVG(语文)
as ‘语文‘,
--通过AVG函数转换英语列
AVG(英语)
as ‘英语‘
from Scores
--转换英语和语文的数据为Score列对应的数据
pivot(
AVG(Score)
for Subject
in (语文,英语) )
as NewScores
group by Student
order by Student
asc
SQL列转换行的两种方法
标签：英语 ... sub 换行 http image case color end
