数据库如何实现表的转置

创作时间:

作者:

@小白创作中心

数据库如何实现表的转置

引用

来源

https://docs.pingcode.com/baike/2098585

在数据库开发和数据分析中，表的转置是一个常见的需求。本文将详细介绍三种实现表转置的方法：PIVOT函数、CASE WHEN语句和动态SQL。通过这些方法，可以有效地实现表的转置操作，使得数据的呈现方式更加灵活和直观。

一、PIVOT函数

使用PIVOT函数是实现表转置的常用方法之一。PIVOT函数通过将行数据转换为列数据，使得数据的呈现方式更加灵活和直观。以下是使用PIVOT函数的详细步骤：

基本概念

PIVOT函数将数据从行转换为列，通常用于汇总数据或生成交叉表。PIVOT函数需要指定三个要素：聚合函数、要转换的列、要生成的新列。

示例表

假设有一个示例表 Sales，其结构如下：

CREATE TABLE Sales (
    Product VARCHAR(50),  
    Year INT,  
    Amount DECIMAL(10, 2)  
);
INSERT INTO Sales (Product, Year, Amount) VALUES  
('ProductA', 2020, 1000.00),  
('ProductA', 2021, 1500.00),  
('ProductB', 2020, 2000.00),  
('ProductB', 2021, 2500.00);

PIVOT查询

使用PIVOT函数将 Sales 表中的数据转置，使得每个产品的销售额按年份显示在不同的列中。

SELECT Product, [2020] AS Year2020, [2021] AS Year2021
FROM (  
    SELECT Product, Year, Amount  
    FROM Sales  
) AS SourceTable  
PIVOT (  
    SUM(Amount)  
    FOR Year IN ([2020], [2021])  
) AS PivotTable;

二、CASE WHEN语句

使用CASE WHEN语句也是实现表转置的常用方法之一。通过CASE WHEN语句，可以灵活地将行数据转换为列数据。

基本概念

CASE WHEN语句通过条件判断来实现数据的转置。通常用于简单的表转置操作。

示例表

假设有一个示例表 Sales，其结构与上面的示例表相同。

CASE WHEN查询

使用CASE WHEN语句将 Sales 表中的数据转置，使得每个产品的销售额按年份显示在不同的列中。

SELECT
    Product,  
    SUM(CASE WHEN Year = 2020 THEN Amount ELSE 0 END) AS Year2020,  
    SUM(CASE WHEN Year = 2021 THEN Amount ELSE 0 END) AS Year2021  
FROM Sales  
GROUP BY Product;

三、动态SQL

使用动态SQL可以实现更加复杂的表转置操作。动态SQL通过生成并执行动态的SQL语句来实现数据的转置。

基本概念

动态SQL通过生成动态的SQL语句来实现复杂的表转置操作。适用于需要根据数据动态生成列的情况。

示例表

假设有一个示例表 Sales，其结构与上面的示例表相同。

动态SQL查询

使用动态SQL将 Sales 表中的数据转置，使得每个产品的销售额按年份显示在不同的列中。

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @columns = N''  
SELECT @columns += N', p.' + QUOTENAME(Year)  
FROM (SELECT DISTINCT Year FROM Sales) AS x;  
SET @sql = N'  
SELECT Product' + @columns + '  
FROM   
(  
    SELECT Product, Year, Amount  
    FROM Sales  
) AS j  
PIVOT  
(  
    SUM(Amount) FOR Year IN (' + STUFF(@columns, 1, 2, '') + ')  
) AS p;';  
EXEC sp_executesql @sql;