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/















留言

這個網誌中的熱門文章

何謂淨重(Net Weight)、皮重(Tare Weight)與毛重(Gross Weight)

Architecture(架構) 和 Framework(框架) 有何不同?_軟體設計前的事前規劃的藍圖概念

(2021年度)駕訓學科筆試準備題庫歸納分析_法規是非題