SQL动态多值透视（Multiple Value Dynamic Pivot）问题及实现需求

现有#BySite表存储自2021年10月1日起每月末的快照数据，PlayMonth及之后列是各月玩家数据。例如某玩家2024年1-3月的记录会有多行，每行对应不同月份的玩法数据。需求是通过动态PIVOT将行转为动态列，生成如Jan_Theo、Jan_Actual等列，每行对应一个月份，早于该行月份的列值为NULL，需透视的字段包括ClubLevel、Property、Trips、Theo、Actual、FP，最终实现用户可选择任意两个月对比玩家数据的报表。

当前仅测试Theo字段时动态PIVOT代码可正常运行，但添加Actual字段及第二个PIVOT后出现以下错误：

Msg 207, Level 16, State 1, Line 129
Invalid column name 'PlayMonth'.
Msg 265, Level 16, State 1, Line 129
The column name "2021|11" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.
Msg 265, Level 16, State 1, Line 129
The column name "2021|12" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.

问题核心是多字段直接PIVOT会导致列名冲突，且二次PIVOT时丢失PlayMonth上下文。正确做法是先通过UNPIVOT将多指标字段转为键值对，再统一PIVOT：

1. 先做UNPIVOT拆解多指标
   将需要透视的ClubLevel、Property、Trips、Theo、Actual、FP字段，与PlayMonth结合，生成包含MetricType（指标名）、MetricValue（指标值）的中间结果，避免后续PIVOT列名重复。
   示例代码片段：

SELECT 
    PlayerID, -- 玩家唯一标识列
    PlayMonth,
    MetricType = CONCAT(MONTH_NAME, '_', Metric),
    MetricValue
FROM (
    SELECT 
        PlayerID,
        PlayMonth,
        MONTH_NAME = DATENAME(MONTH, PlayMonth),
        ClubLevel,
        Property,
        Trips,
        Theo,
        Actual,
        FP
    FROM #BySite
) src
UNPIVOT (
    MetricValue FOR Metric IN (ClubLevel, Property, Trips, Theo, Actual, FP)
) unpvt

2. 动态生成PIVOT列名
   基于UNPIVOT后的MetricType生成唯一列名（如Jan_Theo、Feb_Actual），避免重复：

DECLARE @PivotColumns NVARCHAR(MAX)
SELECT @PivotColumns = STRING_AGG(QUOTENAME(MetricType), ', ')
FROM (
    SELECT DISTINCT CONCAT(DATENAME(MONTH, PlayMonth), '_', Metric) AS MetricType
    FROM #BySite
    CROSS JOIN (VALUES ('ClubLevel'),('Property'),('Trips'),('Theo'),('Actual'),('FP')) AS Metrics(Metric)
) col_list

3. 执行动态PIVOT
   用生成的列名执行PIVOT，同时保留PlayMonth作为行标识，确保早于当前行月份的列值为NULL（可通过在UNPIVOT时过滤，或在最终结果中用CASE判断）：

DECLARE @DynamicSQL NVARCHAR(MAX)
SET @DynamicSQL = N'
SELECT 
    PlayMonth,
    ' + @PivotColumns + N'
FROM (
    SELECT 
        PlayerID,
        PlayMonth,
        MetricType = CONCAT(DATENAME(MONTH, PlayMonth), ''_'', Metric),
        MetricValue,
        -- 用于过滤早于当前月份的数据，确保列值为NULL
        CurrentMonth = EOMONTH(PlayMonth)
    FROM (
        SELECT 
            PlayerID,
            PlayMonth,
            DATENAME(MONTH, PlayMonth) AS MONTH_NAME,
            ClubLevel,
            Property,
            Trips,
            Theo,
            Actual,
            FP
        FROM #BySite
    ) src
    UNPIVOT (
        MetricValue FOR Metric IN (ClubLevel, Property, Trips, Theo, Actual, FP)
    ) unpvt
) pvt_src
PIVOT (
    MAX(MetricValue) FOR MetricType IN (' + @PivotColumns + N')
) pvt
-- 若需要按玩家分组对比，可添加GROUP BY PlayerID, PlayMonth
'
EXEC sp_executesql @DynamicSQL

4. 月份对比逻辑
   最终报表中，用户选择任意两个月时，可通过筛选PlayMonth为目标月份，或在动态SQL中加入参数化的月份筛选，只保留需要对比的行，同时确保对应月份的指标列显示数据，其他月份列显示NULL。

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