无需Cursor与Pivot，如何动态实现纵向数据转横向？

我在存储过程中用Cursor实现纵向数据到横向的动态转换，但执行速度太慢，想优化方案。因数据映射分散无法使用Pivot，请问除Cursor外有什么更好的方法？

已完成的工作 #

已创建三个临时表：#tablestructure、#raw、#dsid。#tablestructure包含目标表的所有字段定义，创建表后需从#raw中按行号、列号将数据插入新表。

临时表结构与样本数据 #

#tablestructure表

#raw表

#DSID表

预期输出 #

现有游标实现代码 #

declare createSQL cursor for 
select distinct 'drop table TestTmp.dbo.' + t.destname  as droptableSQL,
         'CREATE TABLE TestTmp.dbo.' + t.destname    + ' (' + stuff((
            select ',[' + destfieldname + '] ' + f.datatype from #tableStructure f 
            where (f.desttablename in (t.desttablename, '*')) 
            group by destfieldname, f.datatype) 
    for xml path ('')),1,1,'') + ', [sequenceID] int, [subsequenceID] int)'  as createTableSQL,
'insert into VectorNormalizerTmp.dbo.' + t.desttablename + @tablesuffix + '([' + replace(replace(respondentidfieldname,']',''),'[','') + '],sequenceid, subsequenceid) 
SELECT a.contents, b.sequenceid, b.subsequenceid from (select contents from #raw where fieldname = ''' + respondentidfieldname + ''') a 
CROSS JOIN (select sequenceid, subsequenceid from #rowUniques where desttablename = ''' + desttablename +''') b ' as appendEmptyRowsSQL,
'create index idx' + t.desttablename + @tablesuffix + ' on VectorNormalizerTmp.dbo.' + desttablename + @tablesuffix + '([' + respondentidfieldname + '],sequenceid, subsequenceid)' as idxSQL

from (select distinct desttablename from #tablestructure where desttablename = 'Screener') t
cross join (select respondentidfieldname, debug from #dsid) d


declare @sqltorun nvarchar(max);
declare @sqltorun2 nvarchar(max);
declare @sqltorun3 nvarchar(max);
declare @sqltorun4 nvarchar(max);

open createSQL
fetch next from createSQL into @sqltorun, @sqltorun2, @sqltorun3, @sqltorun4
while @@FETCH_STATUS = 0 
begin
    begin try
    --print @sqltorun
    EXECUTE sp_executesql @sqltorun
    end try
    begin catch
    --print '!!ERROR: ' + ERROR_MESSAGE()   
    end catch
    print @sqltorun2
    EXECUTE sp_executesql @sqltorun2
    print @sqltorun3
    EXECUTE sp_executesql @sqltorun3
    --print @sqltorun4
    begin try
    EXECUTE sp_executesql @sqltorun4
    end try
    begin catch
    --print '!!ERROR: ' + ERROR_MESSAGE()   
    end catch
    fetch next from createSQL into @sqltorun, @sqltorun2, @sqltorun3, @sqltorun4
end
close createSQL
deallocate createSQL

游标性能差的核心原因是逐行处理，改成集合式操作+动态SQL批量生成能大幅提升效率，以下是具体方案：

1. 批量生成并执行SQL语句 #

不需要用游标循环逐行生成执行SQL，直接拼接所有需要的DDL/DML语句，一次性调用sp_executesql执行，减少多次编译和执行的开销。

示例代码 #

DECLARE @fullSQL NVARCHAR(MAX) = N'';

-- 批量生成所有操作语句
SELECT @fullSQL += 
    -- 处理DROP TABLE（捕获异常避免报错）
    N'BEGIN TRY EXEC sp_executesql N''' + REPLACE(droptableSQL, '''', '''''') + N'''; END TRY BEGIN CATCH END CATCH;' + CHAR(13) + CHAR(10)
    -- 创建目标表
    + REPLACE(createTableSQL, '''', '''''') + N';' + CHAR(13) + CHAR(10)
    -- 插入空行数据
    + REPLACE(appendEmptyRowsSQL, '''', '''''') + N';' + CHAR(13) + CHAR(10)
    -- 创建索引（捕获异常避免报错）
    + N'BEGIN TRY EXEC sp_executesql N''' + REPLACE(idxSQL, '''', '''''') + N'''; END TRY BEGIN CATCH END CATCH;' + CHAR(13) + CHAR(10)
FROM (
    select distinct 
        'drop table TestTmp.dbo.' + t.destname  as droptableSQL,
        'CREATE TABLE TestTmp.dbo.' + t.destname    + ' (' + stuff((
            select ',[' + destfieldname + '] ' + f.datatype from #tableStructure f 
            where (f.desttablename in (t.desttablename, '*')) 
            group by destfieldname, f.datatype) 
        for xml path ('')),1,1,'') + ', [sequenceID] int, [subsequenceID] int)'  as createTableSQL,
        'insert into VectorNormalizerTmp.dbo.' + t.desttablename + @tablesuffix + '([' + replace(replace(respondentidfieldname,']',''),'[','') + '],sequenceid, subsequenceid) 
        SELECT a.contents, b.sequenceid, b.subsequenceid from (select contents from #raw where fieldname = ''' + respondentidfieldname + ''') a 
        CROSS JOIN (select sequenceid, subsequenceid from #rowUniques where desttablename = ''' + desttablename +''') b ' as appendEmptyRowsSQL,
        'create index idx' + t.desttablename + @tablesuffix + ' on VectorNormalizerTmp.dbo.' + desttablename + @tablesuffix + '([' + respondentidfieldname + '],sequenceid, subsequenceid)' as idxSQL
    from (select distinct desttablename from #tablestructure where desttablename = 'Screener') t
    cross join (select respondentidfieldname, debug from #dsid) d
) AS SQLStatements;

-- 一次性执行所有SQL
EXEC sp_executesql @fullSQL;

2. 用动态PIVOT直接生成目标数据 #

虽然数据映射分散，但可基于rownumber（对应目标行）做动态PIVOT，直接将#raw数据转置为目标表结构，跳过空行插入+后续更新的冗余步骤，减少IO开销。

示例代码（以sample表为例） #

DECLARE @targetTable NVARCHAR(128) = 'sample';
DECLARE @columns NVARCHAR(MAX);
DECLARE @pivotSQL NVARCHAR(MAX);

-- 获取目标表的所有字段列表
SELECT @columns = STRING_AGG(QUOTENAME(destfieldname), ',')
FROM #tablestructure
WHERE desttablename IN (@targetTable, '*');

-- 生成动态PIVOT插入语句
SET @pivotSQL = N'
INSERT INTO TestTmp.dbo.' + @targetTable + '(' + @columns + ', sequenceID, subsequenceID)
SELECT ' + @columns + ', sequenceID, subsequenceID
FROM (
    SELECT 
        r.rownumber,
        r.fieldname,
        r.contents,
        ru.sequenceID,
        ru.subsequenceID
    FROM #raw r
    JOIN #rowUniques ru ON r.rownumber = ru.rownumber -- 假设rowUniques的rownumber与raw的rownumber对应
    JOIN #tablestructure ts ON r.fieldname = ts.destfieldname 
        AND ts.desttablename IN (''' + @targetTable + ''', ''*'')
) AS SourceData
PIVOT (
    MAX(contents)
    FOR fieldname IN (' + @columns + ')
) AS PivotTable;';

EXEC sp_executesql @pivotSQL;

3. 额外性能优化点 #

• 临时表加索引：给#raw的rownumber、fieldname字段加非聚集索引，给#tablestructure的desttablename、destfieldname加索引
• 关闭自动统计更新：批量操作前执行SET AUTO_UPDATE_STATISTICS OFF;，操作完成后再恢复
• 合理选择临时表类型：若需跨会话使用，可考虑全局临时表或持久化表（注意并发冲突）

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