SQL Server环境下高效数据逆透视方案咨询（含SSIS及替代工具）

一、SSIS中用C#脚本组件实现自动逆透视 #

完全可以用C#脚本组件高效处理，不用手动罗列300+字段，步骤如下：

1. 在SSIS数据流里加脚本组件，选「转换」类型
2. 输入列里勾选需要保留的非透视列（比如你的catgry），以及所有要逆透视的item*列
3. 编辑脚本，在Input0_ProcessInputRow方法里遍历目标列生成新行：

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    // 提取非透视列值
    string catgryValue = Row.catgry;

    // 遍历所有要逆透视的列（匹配以"item"开头的列名）
    foreach (IDTSInputColumn100 col in this.ComponentMetaData.InputCollection[0].InputColumnCollection)
    {
        if (col.Name.StartsWith("item"))
        {
            // 创建输出行
            Output0Buffer.AddRow();
            Output0Buffer.catgry = catgryValue;
            Output0Buffer.items = col.Name;
            // 读取列值，注意匹配实际数据类型，这里示例用字符串
            var colValue = Row.GetType().GetProperty(col.Name).GetValue(Row, null);
            Output0Buffer.value = colValue?.ToString();
        }
    }
}

4. 配置输出列：添加catgry（和输入同类型）、items（字符串）、value（根据实际数据类型调整）
   这种方法自动识别符合规则的列，不用手动维护字段列表，就算后续列数量变化也不用改代码。

二、动态生成T-SQL逆透视脚本（适配SSIS执行） #

如果SSIS里执行T-SQL有问题，大概率是用了静态脚本，试试动态生成脚本的方式：

1. 先自动获取所有要逆透视的列名：

DECLARE @pivotColumns NVARCHAR(MAX)
SELECT @pivotColumns = STRING_AGG(QUOTENAME(name), ',')
FROM sys.columns
WHERE object_id = OBJECT_ID('你的原始表名')
AND name LIKE 'item%' -- 匹配所有item开头的列

2. 生成并执行逆透视语句，直接插入暂存区：

DECLARE @sql NVARCHAR(MAX)
SET @sql = N'
INSERT INTO 你的暂存表名(catgry, items, value)
SELECT catgry, items, value
FROM 你的原始表名
UNPIVOT
(
    value FOR items IN (' + @pivotColumns + ')
) AS unpvt'

EXEC sp_executesql @sql

在SSIS里用执行SQL任务运行这个脚本即可，注意给执行账号分配sys.columns访问权限和表读写权限。

三、SSIS逆透视组件的动态配置 #

SSIS自带的逆透视组件也能实现自动列识别，不用手动选列：

1. 加一个脚本任务，用C#读取目标表的列结构，生成符合SSIS格式的列列表（比如"item1", "item2", ...），赋值给SSIS变量
2. 打开逆透视组件的属性，找到UnpivotColumns，用表达式绑定到这个变量
   这种方法适合不想写复杂转换脚本，但要自动化列识别的场景。

四、企业级自动化方案（SSISDB配合存储过程） #

如果是长期维护的任务，建议把动态逆透视逻辑封装成存储过程：

1. 创建存储过程，内部实现动态列获取、逆透视、插入暂存区的逻辑
2. 在SSIS包中调用这个存储过程，或者用SQL代理作业定期触发
   好处是逻辑统一维护，不用在SSIS包中嵌套复杂脚本。

实践案例参考 #

以你给出的示例数据为例，动态T-SQL脚本会自动识别item1、item2，输出结果和你要的逆透视格式完全一致；用C#脚本组件的话，只要列名符合item开头的规则，不管是2列还是300列，都会自动遍历生成目标行，不需要修改代码。

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