Sql Sercer 行转列 列转行

Create table Student
(
  Id int primary key identity,
  KM varchar(50),
  Name varchar(52),
  Score int
)
select * from student

列转行
/*SELECT [姓名],
 max(CASE 课程 WHEN '语文' THEN 分数 ELSE 0 end) AS 语文,
 max(CASE 课程 WHEN '数学' THEN 分数 ELSE 0 end)AS 数学,
 max(CASE 课程 WHEN '物理' THEN 分数 ELSE 0 end)AS 英语,
FROM student GROUP BY [姓名]*/
SELECT Name,
 max(CASE KM WHEN '语文' THEN Score ELSE 0 end) AS 语文,
 max(CASE KM WHEN '数学' THEN Score ELSE 0 end)AS 数学,
 max(CASE KM WHEN '物理' THEN Score ELSE 0 end)AS 英语
FROM student GROUP BY Name


--使用PIVOT列转行    
select * from
(
  SELECT    Score ,
            KM ,
            Name
          FROM   student
)
student pivot(max(score) for km in (语文,数学,英语)) t



Create table StudentTwo
(
  Id int primary key identity,
  Name varchar(52),
  Yuwen Int,
  Shuxue int,
  Yingyu int,
)
select * from StudentTwo

--列转行
SELECT * FROM (
    SELECT Name AS '姓名', '语文' AS '科目', Yuwen AS '成绩' FROM StudentTwo
    UNION ALL
    SELECT Name AS '姓名', '数学' AS '科目', Shuxue AS '成绩' FROM StudentTwo
    UNION ALL
    SELECT Name AS '姓名', '英语' AS '科目', Yingyu AS '成绩' FROM StudentTwo
    )t

select * from
(
  select name as '姓名', '语文' as '科目'  ,yuwen as '成绩' from studenttwo

)t

--使用UNPIVOT列转行
SELECT *  from StudentTwo UNPIVOT(成绩 FOR 科目 IN(Yuwen,Shuxue,Yingyu)) T