如何在动态创建列的同时执行UNPIVOT操作

原始数据表 #

期望输出数据表 #

请问是否可以在对第一张表执行UNPIVOT操作的同时，为每个Forecast_ID动态创建新列？

可以实现目标，但无法一步同时完成UNPIVOT和动态创建Forecast_ID列，需要通过「先UNPIVOT、再PIVOT」的组合操作达成需求：先把宽表中的Measure_1/Measure_2转成行记录，再把Forecast_ID的值转成列。

以SQL为例，具体操作如下：

第一步：UNPIVOT 转换度量列 #

先将原始表的度量列转换为行记录，得到中间结果：

SELECT 
    Date_ID,
    Customer_ID,
    Forecast_ID,
    Measure_Type,
    Measure_Value
FROM 
    原始表
UNPIVOT (
    Measure_Value FOR Measure_Type IN (Measure_1, Measure_2)
) AS UnpivotedData

中间结果如下：

第二步：PIVOT 转换Forecast_ID列 #

基于中间结果，将Forecast_ID的值转换为列，得到目标输出：

SELECT 
    Date_ID,
    Customer_ID,
    Measure_Type,
    FC1,
    FC2
FROM 
    (
        SELECT 
            Date_ID,
            Customer_ID,
            Forecast_ID,
            Measure_Type,
            Measure_Value
        FROM 
            原始表
        UNPIVOT (
            Measure_Value FOR Measure_Type IN (Measure_1, Measure_2)
        ) AS UnpivotedData
    ) AS Intermediate
PIVOT (
    MAX(Measure_Value) FOR Forecast_ID IN (FC1, FC2)
) AS PivotedData

动态适配新增的Forecast_ID #

如果需要自动适配未来新增的Forecast_ID（如FC3、FC4），可以用动态SQL生成列列表，以SQL Server为例：

DECLARE @ForecastColumns NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)

-- 动态获取所有唯一Forecast_ID作为列
SELECT @ForecastColumns = STRING_AGG(QUOTENAME(Forecast_ID), ',')
FROM (SELECT DISTINCT Forecast_ID FROM 原始表) AS Forecasts

-- 拼接并执行动态SQL
SET @SQL = N'
SELECT 
    Date_ID,
    Customer_ID,
    Measure_Type,
    ' + @ForecastColumns + N'
FROM 
    (
        SELECT 
            Date_ID,
            Customer_ID,
            Forecast_ID,
            Measure_Type,
            Measure_Value
        FROM 
            原始表
        UNPIVOT (
            Measure_Value FOR Measure_Type IN (Measure_1, Measure_2)
        ) AS UnpivotedData
    ) AS Intermediate
PIVOT (
    MAX(Measure_Value) FOR Forecast_ID IN (' + @ForecastColumns + N')
) AS PivotedData
'

EXEC sp_executesql @SQL

总结：无法一步同时完成两个转换，但通过「UNPIVOT+动态PIVOT」的组合操作，完全可以实现你需要的结果。

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