MySQL 中的資料透視表
在本文中，我們將介紹如何將表資料從行轉換為列。這個過程稱為旋轉。這種轉換的結果通常是一個彙總表，我們在其中提供適合報告生成的資訊。
在 MySQL 中，沒有建立資料透視表的內建函式，因此你必須編寫一個 MySQL 查詢來生成資料透視表。幸運的是，有三種不同的方法可以使用 MySQL 建立資料透視表。
- 在 MySQL 中使用
IF語句建立資料透視表 - 使用
CASE語句在 MySQL 中建立資料透視表 - 使用動態資料透視列在 MySQL 中建立資料透視表
以下指令碼建立一個包含三列（name、subjectid 和 marks）的表 student。
create table student(name varchar(20),subjectid int(10), marks int(10));
在這裡，我們將樣本資料值插入到學生表中以進行演示。
insert into student values ('Sam',1,70);
insert into student values ('Sam',2,77);
insert into student values ('Sam',3,71);
insert into student values ('Sam',4,70);
insert into student values ('Sam',1,99);
insert into student values ('John',1,89);
insert into student values ('John',2,87);
insert into student values ('John',3,88);
insert into student values ('John',4,89);
insert into student values ('Martin',1,60);
insert into student values ('Martin',2,47);
insert into student values ('Martin',3,68);
insert into student values ('Martin',4,39);
插入這些值後，讓我們編寫一個 select 查詢來檢視資料庫中的記錄。
Select * from student;
輸出將是：
在 MySQL 中使用 MAX 函式和 IF 語句建立資料透視表
這是解決問題的非常直接的方法。在這種方法中，我們將使用 MAX 函式和 IF 語句。如果資料庫中某個學生的單個科目有多個條目，則最多需要兩分。
現在讓我們看看如何在 SELECT 查詢中使用它：
SELECT name,
MAX(IF(subjectid=1, marks, NULL)) AS Sub1,
MAX(IF(subjectid=2, marks, NULL)) AS Sub2,
MAX(IF(subjectid=3, marks, NULL)) AS Sub3,
MAX(IF(subjectid=4, marks, NULL)) AS Sub4
FROM student
GROUP BY name;
輸出將是：
該輸出是針對每個學生的學科的編譯結果。由於你希望每個學生佔一行，因此你需要按姓名列進行分組。此外，你需要為每一列指定一個條件，即每個主題一個條件。
在 MySQL 中使用 CASE 語句建立資料透視表
這也是解決問題的直接方法。在這種方法中，我們將使用 MAX 函式和 CASE 語句。此外，如果資料庫中的某個學生存在多個針對單個學科的條目，則最多需要兩分。同樣，如果某些學生的分數不存在，則取 NULL 值。
現在讓我們看看如何在 SELECT 查詢中使用它：
SELECT name,
MAX(CASE WHEN subjectid=1 THEN marks ELSE NULL END) AS Sub1,
MAX(CASE WHEN subjectid=2 THEN marks ELSE NULL END) AS Sub2,
MAX(CASE WHEN subjectid=3 THEN marks ELSE NULL END) AS Sub3,
MAX(CASE WHEN subjectid=4 THEN marks ELSE NULL END) AS Sub4
FROM student
GROUP BY name;
輸出將是：
在 MySQL 中使用動態資料透視列建立資料透視表
聚合函式 (MAX)、IF 語句和 CASE 語句生成了上述示例中的資料透視表。使用這種方法的缺點是我們在編寫查詢時需要知道列標題，當列數增加時，程式碼也會增加。我們可以採用上述兩種方法來獲得較小的結果和所有可能的值。
因此，為了克服這些限制，我們可以使用動態樞軸列。在這裡，GROUP_CONCAT 函式可以動態生成 PIVOT 表輸出的列。
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT CONCAT(
' MAX(CASE WHEN subjectid = ', subjectid, ' THEN marks ELSE 0 END)
AS "', subjectid, '"')
)
INTO @sql FROM student;
SET @sql = CONCAT('SELECT name, ', @sql,
' FROM student GROUP BY name');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
在 GROUP_CONCAT 函式中，我們使用 DISTINCT 關鍵字來獲取唯一的標記列表。否則，我們的查詢會因為列表太長而出錯。你可以隨時編寫 Select * from @sql 來檢查動態查詢。
現在，當我們執行這個查詢時，這就是我們得到的結果：
請注意，列標題是根據表中的值動態生成的，並且列標題表示 subjectid。