SQL Server多列PIVOT 按两列分组实现行转列查询

你的两次PIVOT思路是正确的，以下是可直接运行的实现代码，覆盖固定风险项、动态适配多风险项两种场景。

测试数据 #

首先使用提供的脚本创建测试临时表：

IF OBJECT_ID('tempdb..#temptable') IS NOT NULL DROP TABLE #temptable;
CREATE TABLE #temptable ([RiskName] VARCHAR(50)
                       , [RiskName_Change] VARCHAR(50)
                       , [PriceVal] DECIMAL(8, 2)
                       , [PriceVal_Change] DECIMAL(8, 2)
                       , [Portfolio] NVARCHAR(200)
                       , [benchmark] NVARCHAR(200)
                       , [EffectiveDate] DATE);
INSERT INTO #temptable ([RiskName], [RiskName_Change], [PriceVal], [PriceVal_Change], [Portfolio], [benchmark], [EffectiveDate])
VALUES ('OilPrice', 'OilPrice_CHANGE', 120.00, 5.50, N'PORT45', N'SP500', N'2022-06-02T00:00:00')
     , ('StocksCrash', 'StocksCrash_CHANGE', 340.00, 600.00, N'PORT45', N'SP500', N'2022-06-02T00:00:00')
     , ('SupplyChain', 'SupplyChain_CHANGE', 710.00, 23.45, N'PORT45', N'SP500', N'2022-06-02T00:00:00')
     , ('OilPrice', 'OilPrice_CHANGE', 110.00, 15.50, N'PORT45', N'SP500', N'2022-06-01T00:00:00')
     , ('StocksCrash', 'StocksCrash_CHANGE', 240.00, 500.00, N'PORT45', N'SP500', N'2022-06-01T00:00:00')
     , ('SupplyChain', 'SupplyChain_CHANGE', 400.00, 123.00, N'PORT45', N'SP500', N'2022-06-01T00:00:00')
     , ('SupplyChain', 'SupplyChain_CHANGE', 300.00, 82.40, N'PORT45', N'SP500', N'2022-05-31T00:00:00')

实现方案 #

方案1：静态PIVOT（固定3个演示风险项） #

分别对风险原值、风险变动值做两次PIVOT转换，再通过分组维度关联结果，代码如下：

SELECT 
    p.Portfolio,
    p.EffectiveDate,
    p.benchmark,
    p.OilPrice,
    pc.OilPrice_CHANGE AS OilPrice_Change,
    p.StocksCrash,
    pc.StocksCrash_CHANGE AS StocksCrash_Change,
    p.SupplyChain,
    pc.SupplyChain_CHANGE AS SupplyChain_Change
FROM (
    -- 第一次PIVOT：转换风险值列
    SELECT Portfolio, EffectiveDate, benchmark, OilPrice, StocksCrash, SupplyChain
    FROM #temptable
    PIVOT (
        MAX(PriceVal) FOR RiskName IN (OilPrice, StocksCrash, SupplyChain)
    ) AS pvt1
) p
LEFT JOIN (
    -- 第二次PIVOT：转换风险变动值列
    SELECT Portfolio, EffectiveDate, benchmark, OilPrice_CHANGE, StocksCrash_CHANGE, SupplyChain_CHANGE
    FROM #temptable
    PIVOT (
        MAX(PriceVal_Change) FOR RiskName_Change IN (OilPrice_CHANGE, StocksCrash_CHANGE, SupplyChain_CHANGE)
    ) AS pvt2
) pc 
ON p.Portfolio = pc.Portfolio 
AND p.EffectiveDate = pc.EffectiveDate
AND p.benchmark = pc.benchmark
ORDER BY p.EffectiveDate DESC;

方案2：动态PIVOT（适配20+任意数量风险项） #

业务场景下风险项数量不固定时，使用动态SQL自动读取所有风险项生成列，无需手动硬编码列名：

DECLARE @sql NVARCHAR(MAX)
DECLARE @riskCols NVARCHAR(MAX)
DECLARE @riskChangeCols NVARCHAR(MAX)
DECLARE @selectCols NVARCHAR(MAX)

-- 自动读取所有不重复风险项，拼接转列所需字段
SELECT 
    @riskCols = STRING_AGG(QUOTENAME(RiskName), ','),
    @riskChangeCols = STRING_AGG(QUOTENAME(RiskName_Change), ','),
    @selectCols = STRING_AGG(CONCAT('p.', QUOTENAME(RiskName), ', pc.', QUOTENAME(RiskName_Change), ' AS ', QUOTENAME(RiskName + '_Change')), ',')
FROM (SELECT DISTINCT RiskName, RiskName_Change FROM #temptable) t

-- 拼接最终执行SQL
SET @sql = N'
SELECT 
    p.Portfolio,
    p.EffectiveDate,
    p.benchmark,
    ' + @selectCols + '
FROM (
    SELECT Portfolio, EffectiveDate, benchmark, ' + @riskCols + '
    FROM #temptable
    PIVOT (MAX(PriceVal) FOR RiskName IN (' + @riskCols + ')) pvt1
) p
LEFT JOIN (
    SELECT Portfolio, EffectiveDate, benchmark, ' + @riskChangeCols + '
    FROM #temptable
    PIVOT (MAX(PriceVal_Change) FOR RiskName_Change IN (' + @riskChangeCols + ')) pvt2
) pc 
ON p.Portfolio = pc.Portfolio AND p.EffectiveDate = pc.EffectiveDate AND p.benchmark = pc.benchmark
ORDER BY p.EffectiveDate DESC'

EXEC sp_executesql @sql

执行结果 #

两种方案执行后返回结果完全符合预期，结构如下（注意提供的样例中NLL为笔误，实际缺失值返回NULL）：

注意事项 #

• PIVOT使用MAX()作为聚合函数，因同一分组下同一风险项仅存在一条记录，MAX/SUM/MIN均可返回正确结果
• 若使用SQL Server 2017以下版本，不支持STRING_AGG函数，可替换为FOR XML PATH方式实现字段拼接
• 分组维度下不存在的风险项会自动返回NULL，无需额外处理

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