T-SQL筆記22_Pivot 語法彙總SELECT結果的轉置
SQL創建測試用的案例table
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | CREATE TABLE Grades( [Student] VARCHAR(50), [Subject] VARCHAR(50), [Marks] INT ) GO INSERT INTO Grades VALUES ('Jacob','Mathematics',100), ('Jacob','Science',95), ('Jacob','Geography',90), ('Amilee','Mathematics',90), ('Amilee','Science',90), ('Amilee','Geography',100) GO select * from Grades |
成績表目前Column是
學生名稱 , 科目 , 分數這樣子 排列
這裡我想將結果轉置
改為Column是學生名稱 然後 再 By各科目排列各科成績值
而Pivot可以幫助我們達成此任務
PIVOT 必須要搭配一個彙總函式,比方像 SUM()、COUNT()、MAX()等等
(PS: SUM跟COUNT一定要數值類欄位,MAX則可使用在VARCHAR , NVACHAR)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | SELECT * FROM ( SELECT [Student], [Subject], [Marks] FROM Grades ) StudentResults PIVOT ( SUM([Marks]) FOR [Subject] IN ( [Mathematics], [Science], [Geography] ) ) AS PivotTable --可簡寫如下直接SELECT --SELECT * FROM Grades --PIVOT ( -- SUM([Marks]) -- FOR [Subject] -- IN ( -- [Mathematics], -- [Science], -- [Geography] -- ) --) AS PivotTable |
SUM :這裡用於總計Marks欄位的值,因而可被pivot table處理。
FOR :用於告訴pivot 運算子哪一個Column要被翻轉。(從row轉換為column。)
IN : 列出我們想要添加到pivot table列表中的所有不同值。
由於這裡 Subject 列只有三個不同的值,因此我們在列表中為 IN 關鍵字提供了所有三個值。
而這裡也要注意要轉置的column
建議必須先hard-code列出來不然不會被列出
塞入幾筆新科目後的資料
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | INSERT INTO Grades VALUES ('Jacob','History',80), ('Amilee','History',90) GO select * from Grades SELECT * FROM ( SELECT [Student], [Subject], [Marks] FROM Grades ) StudentResults PIVOT ( SUM([Marks]) FOR [Subject] IN ( [Mathematics], [Science], [Geography] ) ) AS PivotTable |
從這可以看到新增的History科目的兩筆紀錄沒被陳列即可證實剛所述的限制。
Ref:
[SQL] Pivot 翻轉Table的方法
https://dotblogs.com.tw/SteveLiu/2019/05/21/173803
T-SQL 旋轉 SELECT 結果,用 PIVOT 就能輕鬆達到需求
https://littlehorseboy.github.io/2020/05/31/2020-t-sql-pivot/#%E5%BB%BA%E7%AB%8B-TempTable-%E7%9A%84%E7%A4%BA%E7%AF%84%E8%B3%87%E6%96%99
Dynamic Pivot Tables in SQL Server
https://www.sqlshack.com/dynamic-pivot-tables-in-sql-server/
SQL Server PIVOT
https://www.sqlservertutorial.net/sql-server-basics/sql-server-pivot/
留言
張貼留言