数据重塑的艺术：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

存储了不同季度的销售额。如果我们希望将这些数据转换为按季度展开的形式，使每个季度的数据都作为单独的一列显示，则需要进行行转列操作。目标表结构如下：
YearQ1Q2Q3Q420231000.001500.001200.001800.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;

结果示例：
YearQuarterAmounts2023Q1: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 中一项非常有用的技能，它能够帮助我们以更加直观的方式展示和分析数据。通过上述方法，您可以根据自己的需求灵活地进行行转列操作。当然，在实际应用中，还需要考虑性能和维护性等因素。