SQL server 查询一个字段行转列

原创

mob649e815b1a71 2024-12-10 07:28:19 ©著作权

©著作权归作者所有：来自51CTO博客作者mob649e815b1a71的原创作品，请联系作者获取转载授权，否则将追究法律责任

SQL Server 中的行转列操作

在数据库管理中，行转列（Pivot）是一种常见的操作，它可以帮助我们以更直观的方式展现数据。在 SQL Server 中，我们可以使用 PIVOT 或者结合条件聚合来实现这一目标。本文将详细介绍如何在 SQL Server 中实现行转列，并提供相应的代码示例。

背景

在实际应用中，我们经常会遇到需要将行数据转换为列数据的场景。例如，在销售数据表中，可能有如下的记录：

销售员	产品	销售额
张三	A	100
张三	B	200
李四	A	150
李四	C	300

如果我们想要将销售员的销售额按产品分列，可以得到如下的结果：

销售员	A	B	C
张三	100	200	NULL
李四	150	NULL	300

使用 PIVOT 实现行转列

在 SQL Server 中，可以使用 PIVOT 子句来实现行转列操作。首先，我们需要创建一个示例数据表并插入数据：

CREATE TABLE SalesData (
    Salesman NVARCHAR(50),
    Product NVARCHAR(50),
    Amount INT
);

INSERT INTO SalesData (Salesman, Product, Amount) VALUES
('张三', 'A', 100),
('张三', 'B', 200),
('李四', 'A', 150),
('李四', 'C', 300);

接下来，我们使用 PIVOT 来实现行转列：

SELECT Salesman, 
       COALESCE([A], 0) AS A, 
       COALESCE([B], 0) AS B, 
       COALESCE([C], 0) AS C
FROM
(
    SELECT Salesman, Product, Amount
    FROM SalesData
) AS SourceTable
PIVOT
(
    SUM(Amount)
    FOR Product IN ([A], [B], [C])
) AS PivotTable;

在上述代码中，SUM(Amount) 将为每名销售员总计销售额，而 FOR Product IN ([A], [B], [C]) 则指定了要转为列的字段。最后，使用 COALESCE 函数处理 NULL 值，确保结果的整洁。

输出结果

执行上述 SQL 查询后，输出结果将会是：

销售员	A	B	C
张三	100	200
李四	150		300

使用条件聚合实现行转列

除了使用 PIVOT，行转列还可以通过条件聚合的方式实现。以下是一个示例代码：

SELECT Salesman,
       SUM(CASE WHEN Product = 'A' THEN Amount ELSE 0 END) AS A,
       SUM(CASE WHEN Product = 'B' THEN Amount ELSE 0 END) AS B,
       SUM(CASE WHEN Product = 'C' THEN Amount ELSE 0 END) AS C
FROM SalesData
GROUP BY Salesman;

在这一代码中，我们使用了 SUM 函数结合 CASE 语句来计算每个销售员的销售额。该方法在某些情况下可以提供更好的灵活性。

旅行图示例

在完成行转列操作的过程中，我们可以将过程视作一次数据迁移旅程。在这个旅程中，数据由源地转换为目标形式，可以用以下图示展示：

journey
    title 数据迁移旅程
    section 数据准备
      创建数据表: 5: 橙色
      插入数据: 5: 橙色
    section 数据转换
      使用 PIVOT: 4: 蓝色
      输出结果: 5: 绿色

甘特图示例

为了更好地理解行转列操作的时间安排，以下是相关的甘特图：

gantt
    title 行转列操作时间表
    dateFormat  YYYY-MM-DD
    section 数据准备
    创建数据表      :a1, 2023-10-01, 1d
    插入数据        :after a1  , 1d
    section 数据转换
    使用 PIVOT       :a2, 2023-10-03, 1d
    输出结果        :after a2, 1d