SQL server横表纵表相互转换

Name	Course	Grade
张三	yuwen	75
张三	shuxue	80
李四	yingyu	90
李四	yuwen	95
李四	shuxue	55

 

 

 

 

 

 

Name	yuwen	shuxue	yingyu
张三	75	80	90
李四	95	55	0

 

 

 

 

 

 

纵表转横表

select Name,
sum(case Course when 'yuwen' then Grade else 0 end) as 'yuwen',
sum(case Course when 'shuxue' then Grade else 0 end) as 'shuxue',
sum(case Course when 'yingyu' then Grade else 0 end) as 'yingyu'
from 纵表
group by Name

 

横表转纵表

SELECT Name,'yuwen' AS 科目,yuwen AS 成绩 FROM 横表 UNION ALL
SELECT Name,'shuxue' AS 科目,shuxue AS 成绩 FROM 横表 UNION ALL
SELECT Name,'yingyu' AS 科目,yingyu AS 成绩 FROM 横表
ORDER BY Name,科目 DESC;

每天学习一点点。