MySQL - 將行轉換為列

Question

我嘗試搜尋帖子，但只找到了 SQL Server/Access 的解決方案。我需要 MySQL (5.X) 中的解決方案。

我有一個包含 3 個欄位的表格（稱為歷史記錄）：hostid、itemname、itemvalue。

如果我執行選擇（select * from History），它將返回

+--------+----------+-----------+
   | hostid | itemname | itemvalue |
   +--------+----------+-----------+
   |   1    |    A     |    10     |
   +--------+----------+-----------+
   |   1    |    B     |     3     |
   +--------+----------+-----------+
   |   2    |    A     |     9     |
   +--------+----------+-----------+
   |   2    |    C     |    40     |
   +--------+----------+-----------+

如何查詢資料庫以傳回類似內容

+--------+------+-----+-----+
   | hostid |   A  |  B  |  C  |
   +--------+------+-----+-----+
   |   1    |  10  |  3  |  0  |
   +--------+------+-----+-----+
   |   2    |   9  |  0  |  40 |
   +--------+------+-----+-----+

#

Answer 1

我將添加一個更長、更詳細的說明來說明解決此問題的步驟。如果太長，我深表歉意。

---

我將從您給出的基礎開始，並用它來定義幾個術語，我將在本文的其餘部分使用這些術語。這將是基底表：

select * from history;

+--------+----------+-----------+
| hostid | itemname | itemvalue |
+--------+----------+-----------+
|      1 | A        |        10 |
|      1 | B        |         3 |
|      2 | A        |         9 |
|      2 | C        |        40 |
+--------+----------+-----------+

這將是我們的目標，漂亮的資料透視表：

select * from history_itemvalue_pivot;

+--------+------+------+------+
| hostid | A    | B    | C    |
+--------+------+------+------+
|      1 |   10 |    3 |    0 |
|      2 |    9 |    0 |   40 |
+--------+------+------+------+

history.hostid 欄位中的值將成為資料透視表中的y 值。 history.itemname 欄位中的值將變為 x-values（出於顯而易見的原因）。

---

當我必須解決創建資料透視表的問題時，我使用三步驟過程來解決它（帶有可選的第四步）：

1. 選擇感興趣的列，即 y 值和x 值
2. 使用額外的列擴展基底表 - 每個 x 值一列
3. 將擴充表分組和聚合 - 每個 y 值一組
4. （可選）美化聚合表

讓我們將這些步驟應用於您的問題，看看會得到什麼：

第 1 步：選擇感興趣的列。在所需的結果中，hostid 提供y 值，itemname 提供x 值。

第 2 步：使用額外列擴充基底表。我們通常需要每個 x 值一列。回想一下，我們的 x 值欄位是 itemname：

create view history_extended as (
  select
    history.*,
    case when itemname = "A" then itemvalue end as A,
    case when itemname = "B" then itemvalue end as B,
    case when itemname = "C" then itemvalue end as C
  from history
);

select * from history_extended;

+--------+----------+-----------+------+------+------+
| hostid | itemname | itemvalue | A    | B    | C    |
+--------+----------+-----------+------+------+------+
|      1 | A        |        10 |   10 | NULL | NULL |
|      1 | B        |         3 | NULL |    3 | NULL |
|      2 | A        |         9 |    9 | NULL | NULL |
|      2 | C        |        40 | NULL | NULL |   40 |
+--------+----------+-----------+------+------+------+

請注意，我們沒有更改行數 - 我們只是添加了額外的列。另請注意 NULL 的模式 - itemname = "A" 的行的新列 A 具有非空值，並且其他新列的空值。

第 3 步：將擴充表進行分組和聚合。我們需要按 hostid 分組，因為它提供了 y 值：

create view history_itemvalue_pivot as (
  select
    hostid,
    sum(A) as A,
    sum(B) as B,
    sum(C) as C
  from history_extended
  group by hostid
);

select * from history_itemvalue_pivot;

+--------+------+------+------+
| hostid | A    | B    | C    |
+--------+------+------+------+
|      1 |   10 |    3 | NULL |
|      2 |    9 | NULL |   40 |
+--------+------+------+------+

（請注意，我們現在每個 y 值一行。） 好的，我們就快到了！我們只需要擺脫那些醜陋的 NULL 即可。

第四步：美化。我們將用零替換任何空值，以便結果集看起來更好：

create view history_itemvalue_pivot_pretty as (
  select 
    hostid, 
    coalesce(A, 0) as A, 
    coalesce(B, 0) as B, 
    coalesce(C, 0) as C 
  from history_itemvalue_pivot 
);

select * from history_itemvalue_pivot_pretty;

+--------+------+------+------+
| hostid | A    | B    | C    |
+--------+------+------+------+
|      1 |   10 |    3 |    0 |
|      2 |    9 |    0 |   40 |
+--------+------+------+------+

我們就完成了－我們已經使用 MySQL 建立了一個漂亮的資料透視表。

---

應用此程序時的注意事項：

• 在額外欄位中使用什麼值。我在此範例中使用了 itemvalue
• 在額外欄位中使用什麼「中性」值。我使用了 NULL，但也可能是 0 或 ""，這取決於您的特定情況
• 分組時使用什麼聚合函數。我使用了sum，但是count 和max 也經常使用（max 經常在建立單行時使用）分佈在多行中的“對象”）
• 使用多列表示 y 值。此解決方案不限於對 y 值使用單一列 - 只需將額外的列插入 group by 子句（並且不要忘記 select他們）

已知限制：

• 此解決方案不允許資料透視表中有 n 列 - 在擴充基底表時需要手動新增每個資料透視列。因此，對於 5 或 10 個 x 值，此解決方案很好。 100塊，不太好。有一些使用預存程序產生查詢的解決方案，但它們很醜陋且很難正確執行。當資料透視表需要有很多列時，我目前不知道有什麼好方法來解決這個問題。