SQL 行轉列 PIVOT 學習示例

CREATE TABLE [StudentScores]
(
[UserName] NVARCHAR(20), --學生姓名
[Subject] NVARCHAR(30), --科目
[Score] FLOAT, --成績
)

INSERT INTO [StudentScores] SELECT '張三', '語文', 80
INSERT INTO [StudentScores] SELECT '張三', '數學', 90
INSERT INTO [StudentScores] SELECT '張三', '英語', 70
INSERT INTO [StudentScores] SELECT '張三', '生物', 85
INSERT INTO [StudentScores] SELECT '李四', '語文', 80
INSERT INTO [StudentScores] SELECT '李四', '數學', 92
INSERT INTO [StudentScores] SELECT '李四', '英語', 76
INSERT INTO [StudentScores] SELECT '李四', '生物', 88
INSERT INTO [StudentScores] SELECT '碼農', '語文', 60
INSERT INTO [StudentScores] SELECT '碼農', '數學', 82
INSERT INTO [StudentScores] SELECT '碼農', '英語', 96
INSERT INTO [StudentScores] SELECT '碼農', '生物', 78


select * from [StudentScores]

SELECT * FROM [StudentScores] /*數據源*/
AS P 
PIVOT 
(
SUM(Score/*行轉列後 列的值*/) FOR 
p.Subject/*需要行轉列的列*/ IN ([語文],[數學],[英語],[生物]/*列的值*/)
) AS T where username<>'李四'