SQL 行轉列：數據重塑的藝術
在數據庫操作中，有時我們需要將數據從行的格式轉換為列的格式，這種操作被稱為“行轉列”或“旋轉數據”。行轉列的需求在報表生成、數據分析等場景中非常常見。這篇文章將詳細介紹如何在 SQL 中實現行轉列操作，并通過示例代碼進行說明。
什么是行轉列
行轉列指的是將數據庫表中的行數據轉換為列數據的過程。例如，假設我們有如下的表結構和數據：
CREATE TABLE Sales (
Year INT,
Quarter VARCHAR(10),
Amount DECIMAL(10, 2)
);
INSERT INTO Sales (Year, Quarter, Amount) VALUES
(2023, 'Q1', 1000.00),
(2023, 'Q2', 1500.00),
(2023, 'Q3', 1200.00),
(2023, 'Q4', 1800.00);表 Sales 存儲了不同季度的銷售額。如果我們希望將這些數據轉換為按季度展開的形式，使每個季度的數據都作為單獨的一列顯示，則需要進行行轉列操作。目標表結構如下：
Year | Q1 | Q2 | Q3 | Q4 |
2023 | 1000.00 | 1500.00 | 1200.00 | 1800.00 |
行轉列的常見方法
在 MySQL 中，行轉列操作可以通過以下幾種方法實現：
1. 使用條件聚合（CASE WHEN + 聚合函數）
這種方法是最常見且最兼容的方式，通過CASE WHEN語句和聚合函數（如SUM、MAX等）來實現行轉列。
示例：
SELECT
Year,
SUM(CASE WHEN Quarter = 'Q1' THEN Amount ELSE 0 END) AS Q1,
SUM(CASE WHEN Quarter = 'Q2' THEN Amount ELSE 0 END) AS Q2,
SUM(CASE WHEN Quarter = 'Q3' THEN Amount ELSE 0 END) AS Q3,
SUM(CASE WHEN Quarter = 'Q4' THEN Amount ELSE 0 END) AS Q4
FROM
Sales
GROUP BY
Year;適用場景： 條件聚合方法適用于需要對不同條件的數據進行聚合的場景，適用所有 SQL 數據庫。
2. 使用動態 SQL 生成行轉列查詢
對于列的數量不確定或在運行時動態生成時，可以使用動態 SQL 來生成行轉列查詢。
示例：
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'SUM(CASE WHEN Quarter = ''',
Quarter,
''' THEN Amount ELSE 0 END) AS `',
Quarter, '`'
)
) INTO @sql
FROM Sales;
SET @sql = CONCAT('SELECT Year, ', @sql, ' FROM Sales GROUP BY Year');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;適用場景： 動態 SQL 適用于列名不固定，或需要動態生成列名的場景。注意，這種方法較復雜，且 SQL 注入風險需要特別注意。
3. 使用 IF 函數
類似于CASE WHEN，IF函數可以用于簡單的行轉列操作。
示例：
SELECT
Year,
SUM(IF(Quarter = 'Q1', Amount, 0)) AS Q1,
SUM(IF(Quarter = 'Q2', Amount, 0)) AS Q2,
SUM(IF(Quarter = 'Q3', Amount, 0)) AS Q3,
SUM(IF(Quarter = 'Q4', Amount, 0)) AS Q4
FROM
Sales
GROUP BY
Year;適用場景： IF函數語法較簡潔，適用于條件較少且不復雜的行轉列操作。
4. 使用 GROUP_CONCAT 與 SUBSTRING_INDEX（簡單字符串拼接方式）
對于某些簡單的場景，可以通過GROUP_CONCAT與SUBSTRING_INDEX函數將多行拼接成一列，但這種方法更適合小規模數據且無法嚴格控制數據類型。
示例：
SELECT
Year,
GROUP_CONCAT(Quarter, ':', Amount ORDER BY Quarter ASC SEPARATOR ', ') AS QuarterAmounts
FROM
Sales
GROUP BY
Year;結果示例：
Year | QuarterAmounts |
2023 | Q1:1000.00, Q2:1500.00, ... |
適用場景： 適用于快速將多行內容拼接為一列字符串展示的場景，但不適合嚴格的數據分析任務。
5. 使用 JOIN 自連接
在行轉列時，也可以通過多次自連接來實現，但這種方法只適用于行轉列項較少的情況。
示例：
SELECT
t1.Year,
t1.Amount AS Q1,
t2.Amount AS Q2,
t3.Amount AS Q3,
t4.Amount AS Q4
FROM
Sales t1
LEFT JOIN Sales t2 ON t1.Year = t2.Year AND t2.Quarter = 'Q2'
LEFT JOIN Sales t3 ON t1.Year = t3.Year AND t3.Quarter = 'Q3'
LEFT JOIN Sales t4 ON t1.Year = t4.Year AND t4.Quarter = 'Q4'
WHERE
t1.Quarter = 'Q1';適用場景： 自連接適用于列轉換的數量不多且數據規模較小時。隨著列數的增加，這種方法的查詢復雜度和效率問題也會增加。
6. 使用PIVOT關鍵字
在 SQL Server 中，可以使用 PIVOT 關鍵字來簡化行轉列操作。
語法結構：
SELECT
Year,
[Q1],
[Q2],
[Q3],
[Q4]
FROM
(SELECT Year, Quarter, Amount FROM Sales) AS SourceTable
PIVOT
(MAX(Amount) FOR Quarter IN ([Q1], [Q2], [Q3], [Q4])) AS PivotTable;PIVOT 是一種更加直觀的方式，特別是在需要轉列的字段較多時，可以大大簡化查詢語句。
結語
行轉列是 SQL 中一項非常有用的技能，它能夠幫助我們以更加直觀的方式展示和分析數據。通過上述方法，您可以根據自己的需求靈活地進行行轉列操作。當然，在實際應用中，還需要考慮性能和維護性等因素。
