sql多方法实现列转行

CREATE TABLE StudentScore2 (
    [UserName] NVARCHAR(50),
    [语文] FLOAT,
    [数学] FLOAT,
    [英语] FLOAT,
    [物理] FLOAT
)


INSERT INTO StudentScore2(UserName, [语文], [数学], [英语], [物理])
VALUES
('Helen', 100, 90, 85, 80),
('Jack', 75, 90, 85, 100)


SELECT * FROM StudentScore2


-----传统列转行方法 (UNION ALL与GROUP BY)
SELECT UserName, '语文' AS Course, 
        MAX([语文]) AS Score 
FROM StudentScore2 
GROUP BY UserName 
UNION ALL
SELECT UserName, '数学' AS Course, 
        MAX([数学]) AS Score 
FROM StudentScore2 
GROUP BY UserName 
UNION ALL
SELECT UserName, '英语' AS Course, 
        MAX([英语]) AS Score 
FROM StudentScore2 
GROUP BY UserName 
UNION ALL
SELECT UserName, '物理' AS Course, 
        MAX([物理]) AS Score 
FROM StudentScore2 
GROUP BY UserName 




----使用UNPIVOT实现列转行
SELECT UserName, Score, Course
FROM StudentScore2
UNPIVOT
(
    Score FOR Course IN (语文, 数学, 英语, 物理)
) T


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