如何在 MySQL 中將列轉換為行以進行報告？

MySQL 資料透視：將列轉換為行

在 MySQL 等關聯式資料庫中，為了報表目的，常常需要轉換資料。其中一種轉換涉及將列轉換為行，這個過程稱為「透視」。此技術允許更靈活和更簡潔的數據表示。

問題：

考慮以下 MySQL 表：

CREATE TABLE mytable (
  id INT, 
  month VARCHAR(3), 
  col1 VARCHAR(1), 
  col2 VARCHAR(1), 
  col3 VARCHAR(1), 
  col4 VARCHAR(1)
);

INSERT INTO mytable (id, month, col1, col2, col3, col4) VALUES
(101, 'Jan', 'A', 'B', NULL, 'B'),
(102, 'Feb', 'C', 'A', 'G', 'E');

目標是建立一個報表，將 col1 到 col4 的值作為行顯示，月份 Jan 和 Feb 作為列。

解：

MySQL 沒有提供用於反透視或透視資料的內建函數。但是，我們可以使用 UNION ALL 和帶有 CASE 表達式的聚合來模擬這些操作。

1. 反透視：

為了反透視數據，我們建立一個新的子查詢，使用 UNION ALL 將所有欄位中的行組合到單一欄位中：

SELECT id, month, col1 AS `value`, 'col1' AS `descrip`
UNION ALL
SELECT id, month, col2 AS `value`, 'col2' AS `descrip`
UNION ALL
SELECT id, month, col3 AS `value`, 'col3' AS `descrip`
UNION ALL
SELECT id, month, col4 AS `value`, 'col4' AS `descrip`
FROM mytable;

2. 透視：

接下來，我們將反透視查詢包裝在一個子查詢中，並使用聚合和 CASE 語句將資料轉換為所需的格式：

SELECT descrip, 
  MAX(CASE WHEN month = 'Jan' THEN `value` ELSE NULL END) AS Jan,
  MAX(CASE WHEN month = 'Feb' THEN `value` ELSE NULL END) AS Feb
FROM (
  SELECT id, month, `value`, descrip
  FROM (
    SELECT id, month, col1 AS `value`, 'col1' AS `descrip`
    UNION ALL
    SELECT id, month, col2 AS `value`, 'col2' AS `descrip`
    UNION ALL
    SELECT id, month, col3 AS `value`, 'col3' AS `descrip`
    UNION ALL
    SELECT id, month, col4 AS `value`, 'col4' AS `descrip`
    FROM mytable
  ) AS unpivoted
) AS src
GROUP BY descrip;

結果：

Descrip	Jan	Feb
col1	A	C
col2	B	A
col3	NULL	G
col4	B	E

注意：將 ELSE 0 END 改為 ELSE NULL END，使結果更符合資料庫的規範，避免出現不必要的0值。

以上是如何在 MySQL 中將列轉換為行以進行報告？的詳細內容。更多資訊請關注PHP中文網其他相關文章！