基于其他列值创建新列：SSIS导入Excel至SQL Server问题

我之前处理过不少这种Excel行转列的需求，你的场景其实就是把同一个ID下的两行价格数据合并成一行，在SSIS里有几种很实用的解决办法，给你详细说说：

先明确你的数据和目标：

原始Excel数据 #

期望转换结果 #

方法一：用聚合转换（最简洁高效） #

这种方法适合数据结构固定的场景，性能也比脚本好：

1. 先排序数据：在数据流里拖入「排序转换」，按ID升序排序，再按PromotionOrNot排序（顺序无所谓，只要同一个ID的行挨在一起）。这一步是聚合的前提，确保分组能正确识别同一ID的所有行。
2. 添加聚合转换：把排序后的数据流接到聚合转换，设置如下：
   • 将ID列的操作设为 Group By（按ID分组）
   • 新增两个输出列：
     • RegPrice：表达式写 SUM(CASE WHEN PromotionOrNot == "Not" THEN PromotionOrRegPrice ELSE 0 END)
     • PromotionPrice：表达式写 SUM(CASE WHEN PromotionOrNot == "Promotion" THEN PromotionOrRegPrice ELSE 0 END)
       这里用SUM是因为同一个ID下对应状态只有一行，求和就相当于直接取那个值，也可以用MAX或者MIN，效果一样。
3. 把聚合后的输出接到SQL目标表即可。

方法二：脚本转换（适合复杂自定义逻辑） #

如果你需要处理更灵活的规则（比如缺失值处理、多状态扩展），脚本转换更合适：

1. 在数据流里拖入「脚本转换」，选择目标类型（作为数据输出端）。
2. 在脚本编辑器里：
   • 先添加输出列：ID（int类型）、RegPrice（decimal类型）、PromotionPrice（decimal类型）
   • 在类级别声明一个字典来缓存数据（用来临时存储每个ID的两种价格）：

     // 缓存结构：Key=ID，Value=字典（Key=PromotionOrNot，Value=价格）
     Dictionary<int, Dictionary<string, decimal>> priceCache = new Dictionary<int, Dictionary<string, decimal>>();
     

   • 重写ProcessInputRow方法，把每行数据存入缓存：

     public override void ProcessInputRow(Input0Buffer Row)
     {
         // 如果当前ID不在缓存里，先初始化子字典
         if (!priceCache.ContainsKey(Row.ID))
         {
             priceCache[Row.ID] = new Dictionary<string, decimal>();
         }
         // 把当前行的价格存入对应ID的子字典
         priceCache[Row.ID][Row.PromotionOrNot] = Row.PromotionOrRegPrice;
     }
     

   • 重写PostExecute方法，遍历缓存输出合并后的行：

     public override void PostExecute()
     {
         base.PostExecute();
         foreach (var idEntry in priceCache)
         {
             int currentId = idEntry.Key;
             var priceDict = idEntry.Value;
             // 处理缺失值：如果某个状态不存在，设为0或者DBNull（根据你的需求调整）
             decimal regPrice = priceDict.ContainsKey("Not") ? priceDict["Not"] : 0;
             decimal promoPrice = priceDict.ContainsKey("Promotion") ? priceDict["Promotion"] : 0;
     
             // 添加新行并赋值
             Output0Buffer.AddRow();
             Output0Buffer.ID = currentId;
             Output0Buffer.RegPrice = regPrice;
             Output0Buffer.PromotionPrice = promoPrice;
         }
     }
     

3. 保存脚本后，把脚本转换的输出接到SQL目标表即可。

方法三：导入后在SQL Server端处理（灵活易维护） #

如果你的数据量不大，或者后续可能调整转换规则，先把Excel数据导入SQL的临时表，再用SQL语句转换会更方便：

1. 先用SSIS把Excel数据导入临时表（比如#TempPrices），表结构和Excel一致。
2. 执行以下SQL语句转换数据（两种方式选其一）：

   方式A：用PIVOT #

   SELECT 
       ID,
       [Not] AS RegPrice,
       [Promotion] AS PromotionPrice
   FROM 
       #TempPrices
   PIVOT
   (
       MAX(PromotionOrRegPrice)
       FOR PromotionOrNot IN ([Not], [Promotion])
   ) AS PivotResult;
   

   方式B：用条件聚合（更灵活，适合扩展多状态） #

   SELECT
       ID,
       MAX(CASE WHEN PromotionOrNot = 'Not' THEN PromotionOrRegPrice END) AS RegPrice,
       MAX(CASE WHEN PromotionOrNot = 'Promotion' THEN PromotionOrRegPrice END) AS PromotionPrice
   FROM #TempPrices
   GROUP BY ID;
   

3. 把转换后的结果插入目标表即可。

小建议 #

• 如果数据结构固定，优先用聚合转换，性能最好，配置也简单。
• 如果需要处理特殊逻辑（比如某个ID可能只有一种价格、或者有更多状态），用脚本转换或者SQL端处理更灵活。

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