SQL行列转换

SQL行列转换

引言

在数据处理和分析中，经常需要对数据进行行列转换操作，即将行数据转换为列数据或将列数据转换为行数据。SQL作为一种强大的数据查询和处理语言，也可以用来进行行列转换。本文将介绍SQL行列转换的概念、方法和示例代码，并给出详细的运行结果。

什么是行列转换

行列转换是指将数据在行和列之间进行转换的操作。在某些情况下，数据以行的形式存储在数据库中，但我们希望将其以列的形式呈现；或者数据以列的形式存储，但我们需要将其转换为行的形式。行列转换可以使数据更易于理解、分析和处理。

行转列

行转列是将行数据转换为列数据的过程。下面是一些常见的行转列场景：

• 列名存储在行数据中
• 数据分散在多行中
• 透视表的生成

下面我们将通过示例代码来演示如何进行行转列操作。

假设我们有一个学生表，包含学生的学号、姓名和成绩。表格如下：

现在我们希望将学生的学号、姓名和各科成绩转换为列数据，可以使用以下SQL语句进行行转列的操作：

SELECT
    学号,
    姓名,
    CASE WHEN 科目 = '语文' THEN 成绩 ELSE NULL END AS 语文成绩,
    CASE WHEN 科目 = '数学' THEN 成绩 ELSE NULL END AS 数学成绩,
    CASE WHEN 科目 = '英语' THEN 成绩 ELSE NULL END AS 英语成绩
FROM
    (
    SELECT
        学号,
        姓名,
        '语文' AS 科目,
        语文成绩
    FROM
        学生表

    UNION ALL

    SELECT
        学号,
        姓名,
        '数学' AS 科目,
        数学成绩
    FROM
        学生表

    UNION ALL

    SELECT
        学号,
        姓名,
        '英语' AS 科目,
        英语成绩
    FROM
        学生表
    ) AS 转换表

运行结果如下：

通过上述SQL语句，我们将原来的行数据转换为了列数据，每个科目的成绩以对应的列名进行展示，如果某个学生没有对应科目的成绩，则以NULL进行填充。

列转行

列转行是将列数据转换为行数据的过程。下面是一些常见的列转行场景：

• 列名代表某个属性
• 多个列合并为一个列

下面我们将通过示例代码来演示如何进行列转行操作。

假设我们有一个员工表，包含员工的姓名、工号和年度奖金。表格如下：

现在我们希望将员工的姓名、工号和奖金转换为行数据，可以使用以下SQL语句进行列转行的操作：

SELECT
    姓名,
    工号,
    '2021年度奖金' AS 年度,
    `2021年度奖金` AS 奖金
FROM
    员工表

UNION ALL

SELECT
    姓名,
    工号,
    '2022年度奖金' AS 年度,
    `2022年度奖金` AS 奖金
FROM
    员工表

运行结果如下：

通过上述SQL语句，我们将原来的列数据转换为了行数据，每一列的数据以对应的列名作为一个新的列进行展示。

透视表的生成

透视表是基于行列转换的一种特殊形式，可以将数据按照某个字段进行分组，并以此字段的值作为列名。透视表可以更加直观地展示数据关系和汇总信息。

下面我们将通过示例代码来演示如何生成透视表。

假设我们有一个销售订单表，包含订单号、产品类别和销售金额。表格如下：

现在我们希望生成一个透视表，以产品类别为列名，统计每个产品类别的销售金额总和。可以使用以下SQL语句进行透视表的生成：

SELECT
    订单号,
    SUM(CASE WHEN 产品类别 = 'A' THEN 销售金额 ELSE 0 END) AS A类销售金额,
    SUM(CASE WHEN 产品类别 = 'B' THEN 销售金额 ELSE 0 END) AS B类销售金额,
    SUM(CASE WHEN 产品类别 = 'C' THEN 销售金额 ELSE 0 END) AS C类销售金额
FROM
    销售订单表
GROUP BY
    订单号

运行结果如下：

通过上述SQL语句，我们按照产品类别进行分组，对每个产品类别的销售金额进行求和。结果以订单号为行、产品类别为列的形式展示了透视表。

小结

本文介绍了SQL行列转换的概念、方法和示例代码。行列转换可以使数据更易于理解、分析和处理，适用于各种数据处理和分析场景。通过运用行列转换的相关语法，我们可以在SQL中实现表格数据的灵活转换。