SQL 行轉列 PIVOT 學習示例

阿小星發表於2018-12-28
SQL
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<>`李四`

 

相關文章