如何无需循环实现基于动态列的跨表更新#Temp的tempname字段？

我负责维护的应用需通过多张表生成报表：

• Table1存储id及指向配置表列的引用（colnum字段）
• Table2是配置表，包含动态添加的列如usercol1、usercol2……usercol55，用于存储报表相关值

需求是将#Temp表的tempname字段更新为Table2中对应列的值，但常规UPDATE语句无法识别a.colnum作为列名，导致执行失败。

预期报表结果 #

1   bob
2   john
3   jack
4   ben

常规拼接的问题 #

若使用常规内联查询拼接a.usercol + cast(a.colnum as varchar(2))，得到的结果只是字符串拼接，而非对应列的实际值：

1   usercol32
2   usercol2
3   usercol7
4   usercol7

动态SQL尝试的错误 #

尝试用动态SQL编写UPDATE语句时，出现如下错误：

Msg 4104, Level 16, State 1, Line 25
The multi-part identifier "a.colnum" could not be bound.

我可以通过循环结合额外表，将colnum的值存入变量后更新字段，但想请教是否有无需循环的实现方式？

相关DDL代码 #

create table #Table1 (id int, colnum int)
create table #Table2 (id int, usercol1 varchar(20), usercol2 varchar(20), usercol3 varchar(20), usercol7 varchar(20), usercol32 varchar(20))

insert into #Table1(id, colnum) values(1, 32);
insert into #Table1(id, colnum) values(2, 2);
insert into #Table1(id, colnum) values(3, 7);
insert into #Table1(id, colnum) values(4, 7);

insert into #Table2(id, usercol32) values(1, 'bob');
insert into #Table2(id, usercol2) values(2, 'john');
insert into #Table2(id, usercol7) values(3, 'jack');
insert into #Table2(id, usercol7) values(4, 'ben');

CREATE TABLE #Temp    
(    
 tempid int,
 tempname varchar(20) 
)

insert into #Temp (tempid)
select id from #Table1

declare @sql varchar(max)
set @sql = 'update #Temp'
            + ' set tempname = a.usercol' + cast(a.colnum as varchar(2))
            + ' from #table1 a'
            + ' join #table2 b on a.id = b.id'
            + ' where #Temp.tempid = a.id'

exec (@sql)
select * from #Temp

drop table #Temp
drop table #Table1
drop table #Table2

无需循环的解决方案 #

方案一：使用UNPIVOT转换Table2结构 #

将Table2的多列结构转换为行结构，再通过关联查询完成更新，无需动态SQL：

WITH UnpivotedTable2 AS (
    SELECT 
        id,
        colname,
        colvalue
    FROM #Table2
    UNPIVOT (
        colvalue FOR colname IN (usercol1, usercol2, usercol3, usercol7, usercol32)
    ) AS up
)
UPDATE #Temp
SET tempname = ut2.colvalue
FROM #Temp t
JOIN #Table1 t1 ON t.tempid = t1.id
JOIN UnpivotedTable2 ut2 ON t1.id = ut2.id 
    AND ut2.colname = 'usercol' + CAST(t1.colnum AS VARCHAR(2))

此方法适用于Table2的usercol列可枚举的场景，若列数较多（如55个），只需将所有usercol列添加到UNPIVOT的IN子句中即可。

方案二：修正动态SQL写法 #

通过在动态SQL内部构造CASE语句，实现根据colnum匹配对应列，避免循环：

declare @sql varchar(max)
set @sql = 'update #Temp
            set tempname = CASE a.colnum ' + 
            STUFF((SELECT ' WHEN ' + CAST(colnum AS VARCHAR(2)) + ' THEN b.usercol' + CAST(colnum AS VARCHAR(2))
                   FROM (SELECT DISTINCT colnum FROM #Table1) AS nums
                   FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 6, '') +
            ' END
            from #table1 a
            join #table2 b on a.id = b.id
            where #Temp.tempid = a.id'

exec (@sql)

此方法会自动根据Table1中存在的colnum值生成CASE分支，无需手动枚举所有列，适合列动态变化的场景。

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