SQL Server：如何对多列执行动态PIVOT操作？

嘿，我懂你现在的处境——单个动态列的PIVOT已经玩明白了，但碰到两个动态列要转成多列就无从下手了对吧？别慌，咱们用实际例子一步步拆解，把动态多列PIVOT的逻辑讲透。

先从示例场景入手 #

假设咱们有个销售数据表SalesData，结构和数据如下：

-- 示例源表
CREATE TABLE SalesData (
    Region VARCHAR(50),
    ProductCategory VARCHAR(50), -- 第一个需要PIVOT的动态列
    MetricType VARCHAR(50),       -- 第二个需要PIVOT的动态列
    MetricValue DECIMAL(18,2)
);

INSERT INTO SalesData VALUES
('North', 'Electronics', 'Sales', 15000.00),
('North', 'Electronics', 'Profit', 3000.00),
('North', 'Clothing', 'Sales', 8000.00),
('North', 'Clothing', 'Profit', 1600.00),
('South', 'Electronics', 'Sales', 12000.00),
('South', 'Electronics', 'Profit', 2400.00),
('South', 'Clothing', 'Sales', 9500.00),
('South', 'Clothing', 'Profit', 1900.00);

咱们的目标是把ProductCategory和MetricType的所有组合转成列，最终得到按Region分组，每列对应「品类_指标」的统计值。

动态多列PIVOT的核心思路 #

单个动态列PIVOT是直接对某一列的唯一值转列，多列的话，咱们需要先把两个动态列合并成一个组合列，再对这个组合列做动态PIVOT——这就是关键！

具体实现步骤 #

1. 生成动态组合列名 #

首先要把ProductCategory和MetricType的所有唯一组合，拼接成PIVOT需要的列名格式（比如[Electronics_Sales]）。这里用STRING_AGG（SQL Server 2017及以上支持）来自动拼接：

DECLARE @DynamicCols NVARCHAR(MAX), @PivotSQL NVARCHAR(MAX);

-- 生成所有动态组合列名
SELECT @DynamicCols = STRING_AGG(
    QUOTENAME(CONCAT(ProductCategory, '_', MetricType)),
    ', '
)
FROM (
    SELECT DISTINCT ProductCategory, MetricType
    FROM SalesData
) AS UniqueCombos;

如果你的SQL Server版本低于2017，就用FOR XML PATH的方式来拼接：

-- 旧版本SQL Server的列名拼接方式
SELECT @DynamicCols = STUFF((
    SELECT ', ' + QUOTENAME(CONCAT(ProductCategory, '_', MetricType))
    FROM (
        SELECT DISTINCT ProductCategory, MetricType
        FROM SalesData
    ) AS UniqueCombos
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

2. 构造完整的PIVOT动态SQL #

接下来把源数据先处理成包含「组合列」的中间结果，再对这个组合列做PIVOT：

-- 构造PIVOT语句
SET @PivotSQL = N'
SELECT Region, ' + @DynamicCols + N'
FROM (
    SELECT 
        Region,
        -- 把两个动态列合并成一个组合列
        CONCAT(ProductCategory, ''_'', MetricType) AS CombinedCol,
        MetricValue
    FROM SalesData
) AS SourceData
PIVOT (
    -- 根据业务需求选择聚合函数，这里用SUM求和
    SUM(MetricValue)
    FOR CombinedCol IN (' + @DynamicCols + N')
) AS PivotResult;';

3. 执行动态SQL #

最后用sp_executesql执行生成好的SQL语句：

EXEC sp_executesql @PivotSQL;

执行后就能得到咱们想要的结果：每个Region对应所有「品类_指标」的列，值是对应的统计值。

注意事项 #

• 聚合函数选择：根据你的业务场景选合适的聚合函数，比如SUM、AVG、MAX等，确保结果符合需求。
• 列名唯一性：要保证ProductCategory和MetricType的组合是唯一的，避免生成重复列名导致报错。
• SQL注入风险：如果动态列的值来自用户输入，一定要用QUOTENAME转义（咱们示例里已经用了），防止注入攻击。

内容的提问来源于stack exchange，提问作者CarCrazyBen