使用PIVOT函数进行行列转换？

在 SQL 中，PIVOT 函数可以将行数据转换为列数据，实现行列转换。下面通过示例详细介绍其用法。

基本用法示例

假设有一个 Sales 表记录了各地区不同产品的销售额：

Region	Product	Amount
North	Apple	100
North	Banana	150
South	Apple	200
South	Banana	250

使用 PIVOT 将产品列转为列标题，统计各地区销售额：

 

SELECT Region, [Apple], [Banana]
FROM (
    SELECT Region, Product, Amount
    FROM Sales
) AS SourceTable
PIVOT (
    SUM(Amount)  -- 聚合函数
    FOR Product IN ([Apple], [Banana])  -- 指定要转换的列及其值
) AS PivotTable;

 

结果：

Region	Apple	Banana
North	100	150
South	200	250

动态列转换（处理未知列）

如果需要转换的列值不确定（如产品种类不固定），可以使用动态 SQL：

 

DECLARE @Columns NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);

-- 动态获取所有产品名称
SELECT @Columns = STRING_AGG(QUOTENAME(Product), ',')
FROM (SELECT DISTINCT Product FROM Sales) AS Products;

-- 动态构建 PIVOT 查询
SET @SQL = N'
SELECT Region, ' + @Columns + '
FROM (
    SELECT Region, Product, Amount
    FROM Sales
) AS SourceTable
PIVOT (
    SUM(Amount)
    FOR Product IN (' + @Columns + ')
) AS PivotTable;';

EXEC sp_executesql @SQL;

 

关键语法说明

1. 聚合函数：PIVOT 必须搭配聚合函数（如 SUM、COUNT、AVG）使用，因为行转列时需要对数据进行合并。
2. 列值列表：IN 子句中指定的列值将直接成为结果集中的列名。
3. NULL 值处理：如果某些组合没有数据，结果会显示 NULL，可以用 ISNULL() 或 COALESCE() 处理：

   SELECT 
       Region, 
       ISNULL([Apple], 0) AS Apple,  -- 将 NULL 转为 0
       ISNULL([Banana], 0) AS Banana
   FROM ...
   

    

不支持 PIVOT 的数据库（如 MySQL）

在 MySQL 中，可以用 CASE 语句模拟 PIVOT：

 

SELECT
    Region,
    SUM(CASE WHEN Product = 'Apple' THEN Amount ELSE 0 END) AS Apple,
    SUM(CASE WHEN Product = 'Banana' THEN Amount ELSE 0 END) AS Banana
FROM Sales
GROUP BY Region;

 

总结

PIVOT 函数通过以下步骤实现行列转换：

1. 准备源数据（子查询）
2. 指定聚合函数
3. 定义要转换的列及其值
4. 生成结果表

动态 SQL 适用于列值不确定的场景，但需注意 SQL 注入风险。