SQL Server如何实现多列同时PIVOT透视查询

我对PIVOT操作掌握不熟练（此处无双关语意），目前仅完成了一半需求实现，第二部分功能开发遇到阻碍。

测试基础信息 #

• 当前数据展示效果：
  
• 临时表结构及测试数据：

CREATE TABLE #temptable ([PriceName] VARCHAR(50), [PriceName_Change] VARCHAR(50), [PriceVal] DECIMAL(28, 2), [PriceVal_Change] DECIMAL(28, 2), [Portfolio] NVARCHAR(200), [benchmark] NVARCHAR(200) , [EffectiveDate] DATE);
INSERT INTO #temptable ([PriceName], [PriceName_Change], [PriceVal], [PriceVal_Change], [Portfolio], [benchmark], [EffectiveDate])
VALUES 
 ('OilPrice', 'OilPrice_CHANGE', 1607.00, 3.61, N'PORT45', N'SP500',N'2022-06-02T00:00:00')
,('OilPrice', 'OilPrice_CHANGE', 1607.00, 3.61, N'PORT45', N'SP500',N'2022-06-01T00:00:00')
,('OilPrice', 'OilPrice_CHANGE', 607.00, 12.61, N'PORT45', N'SP500',N'2022-05-31T00:00:00')

需求详情 #

业务规则：OilPrice关联取PriceVal列数值，PriceName_Change关联取PriceVal_Change列数值，需要对这两组映射字段同时执行PIVOT透视。
目前仅实现单列PIVOT查询，无法完成双列同时透视效果，已编写的单PIVOT代码如下：

SELECT
    portfolio
  , EffectiveDate
  , [OilPrice]
FROM
    (
        SELECT
             SM.portfolio
           , SM.PriceVal
           , SM.PriceName
           , SM.EffectiveDate
        FROM #temptable SM
    ) AS tbl
PIVOT
(
    SUM(PriceVal)
    FOR PriceName IN ([OilPrice])
) AS pvt;

期望输出结果格式：

OilPrice | OilPrice_Change | Portfolio | Benchmark | EffectiveDate |
1607.00  | 3.61            | PORT45    | SP500     | 2022-06-02    |
1607.00  | 3.61            | PORT45    | SP500     | 2022-06-01    |
607.00   | 12.61           | PORT45    | SP500     | 2022-05-31    |

提供两种可直接运行的实现方案：

方案1：条件聚合（适配当前固定列场景，性能最优） #

当前透视列固定为OilPrice，不需要嵌套PIVOT语法，直接按维度分组做条件聚合即可拿到结果，写法简洁执行效率高：

SELECT
    SUM(CASE WHEN PriceName = 'OilPrice' THEN PriceVal END) AS OilPrice,
    SUM(CASE WHEN PriceName_Change = 'OilPrice_CHANGE' THEN PriceVal_Change END) AS OilPrice_Change,
    Portfolio,
    benchmark AS Benchmark,
    EffectiveDate
FROM #temptable
GROUP BY Portfolio, benchmark, EffectiveDate

方案2：双PIVOT关联（适配后续多列扩展场景） #

如果后续需要新增更多价格类透视字段，可以分别对值列做PIVOT后再按公共维度关联，扩展性更强：

SELECT
    pvt_val.OilPrice,
    pvt_change.OilPrice_CHANGE AS OilPrice_Change,
    pvt_val.Portfolio,
    pvt_val.benchmark AS Benchmark,
    pvt_val.EffectiveDate
FROM
(
    -- 透视PriceVal列
    SELECT Portfolio, benchmark, EffectiveDate, [OilPrice]
    FROM (SELECT Portfolio, benchmark, EffectiveDate, PriceName, PriceVal FROM #temptable) t
    PIVOT (SUM(PriceVal) FOR PriceName IN ([OilPrice])) p_val
) pvt_val
INNER JOIN
(
    -- 透视PriceVal_Change列
    SELECT Portfolio, benchmark, EffectiveDate, [OilPrice_CHANGE]
    FROM (SELECT Portfolio, benchmark, EffectiveDate, PriceName_Change, PriceVal_Change FROM #temptable) t
    PIVOT (SUM(PriceVal_Change) FOR PriceName_Change IN ([OilPrice_CHANGE])) p_change
) pvt_change
ON pvt_val.Portfolio = pvt_change.Portfolio 
AND pvt_val.benchmark = pvt_change.benchmark 
AND pvt_val.EffectiveDate = pvt_change.EffectiveDate

两种方案执行结果完全匹配预期格式，当前场景优先选择方案1即可。

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