SQL PIVOT函数使用详解

在 SQL 里，PIVOT 函数的主要作用是把行数据转换为列数据，也就是进行行转列操作。这在需要生成交叉表或者进行数据透视分析时非常实用。下面为你详细介绍它的用法。

基本语法

SELECT <非透视列>,
    [第一个透视列的值] AS <列别名>,
    [第二个透视列的值] AS <列别名>,
    ...
FROM
    (<包含行转列数据的 SQL 查询>) AS <源查询别名>
PIVOT
(
    <聚合函数>(<需要聚合的列>)
    FOR [<需要行转列的列>] IN ([第一个透视列的值], [第二个透视列的值], ...)
) AS <透视表别名>
ORDER BY <排序依据列>;

 

参数说明

• 源查询：这是一个子查询，其结果集为 PIVOT 操作提供输入数据。
• 聚合函数：像 SUM、COUNT、AVG 这类函数，用于对数据进行聚合计算。
• 需要聚合的列：该列的数据会被聚合函数处理。
• 需要行转列的列：此列中的不同值会被转换为结果集中的列。
• 透视列的值：这是一个列表，明确了哪些值会被转换为列。

使用示例

假设有一个 Sales 表，其结构和数据如下：

 

CREATE TABLE Sales (
    Region VARCHAR(50),
    Product VARCHAR(50),
    Amount INT
);

INSERT INTO Sales (Region, Product, Amount) VALUES
('North', 'Apple', 100),
('North', 'Banana', 150),
('South', 'Apple', 200),
('South', 'Banana', 250);

 

示例 1：基本的 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

示例 2：动态生成透视列

要是不知道具体有哪些产品，需要动态生成透视列，可以结合 SQL 的动态执行功能来实现：

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

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

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

-- 执行动态生成的 SQL
EXEC sp_executesql @SQL;

 

注意事项

1. 性能方面：PIVOT 会对结果进行隐式排序，这可能会影响查询性能。
2. 空值处理：如果某些组合没有数据，结果中可能会出现 NULL，可以使用 ISNULL() 函数进行处理。
3. 兼容性问题：并非所有的 SQL 数据库都支持 PIVOT 语法，例如 MySQL 就需要用 CASE 语句来模拟。
4. 替代方案：可以使用 CASE 语句手动实现行转列，示例如下：

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 函数是 SQL 中进行数据透视分析的强大工具，它能够让数据的展示更加直观。不过，在使用时要充分考虑性能和兼容性问题。