sql多方法实现行转列demo

CREATE TABLE StudentScore (
    UserName NVARCHAR(50),
    Course NVARCHAR (50),
    Score FLOAT
)


INSERT INTO StudentScore(UserName, Course, Score)
VALUES
('Helen', '语文', 100),
('Helen', '数学', 90),
('Helen', '英语', 85),
('Helen', '物理', 80),
('Jack', '语文', 75),
('Jack', '数学', 90),
('Jack', '英语', 85),
('Jack', '物理', 100)


SELECT * FROM StudentScore




----传统行转列的实现方法如下
SELECT UserName, 
      MAX(CASE Course WHEN '语文' THEN Score ELSE 0 END) AS '语文',
      MAX(CASE Course WHEN '数学' THEN Score ELSE 0 END) AS '数学',
      MAX(CASE Course WHEN '英语' THEN Score ELSE 0 END) AS '英语',
      MAX(CASE Course WHEN '物理' THEN Score ELSE 0 END) AS '物理'
FROM StudentScore
GROUP BY UserName


-----使用PIVOT数据透视的方法实现行转列


SELECT UserName, 语文, 数学, 英语, 物理
FROM StudentScore
PIVOT
(
    SUM(Score) FOR Course IN (语文, 数学, 英语, 物理)
) T


--使用 * 也可以
SELECT *
FROM StudentScore
PIVOT
(
    SUM(Score) FOR Course IN (语文, 数学, 英语, 物理)
) T