基于动态列名获取数据表列值的技术实现问询

现有两张临时表#ColumnTable（列指定表）和#DataTable（源数据表）。#ColumnTable存储不同cid对应的需获取的列名，#DataTable按cid存储各列数据，每个cid仅对应一行数据。需求是根据cid匹配，将#DataTable中对应列的值更新到#ColumnTable的colValue字段。目前尝试过逐列查询的动态SQL，寻求更优实现方案。

表结构及测试数据 #

#ColumnTable创建及插入语句 #

create table #ColumnTable (
    cid integer,
    columnName varchar(20),
    colValue integer);

insert into #ColumnTable (
    cid,
    columnName)
values
    (23, 'col1'),
    (23, 'col2'),
    (23, 'col3'),
    (34, 'col1'),
    (34, 'col3'),
    (43, 'col2'),
    (43, 'col1'),
    (43, 'col4'),
    (44, 'col2');

#DataTable创建及插入语句 #

create table #DataTable (
     cid  integer
   , col1 integer
   , col2 integer
   , col3 integer
   , col4 integer
   , col5 integer);

insert into #DataTable (
    cid
  , col1
  , col2
  , col3
  , col4
  , col5)
values
    (23, 1,   2,  3,  4, 37),
    (34, 7,  10, 13,  4, 3),
    (43, 5,   2,  4,  4, 35),
    (44, 19, 12, 13, 24, 53);

预期更新结果 #

cid columnName  colValue
23  col1         1
23  col2         2
23  col3         3
34  col1         7
34  col3        13
43  col2         2
43  col1         5
43  col4         4
44  col2        12

最优实现方案 #

方案1：UNPIVOT + 关联更新（静态列场景） #

通过UNPIVOT将#DataTable的列转换为行格式，直接与#ColumnTable关联更新，无需动态SQL，性能和可读性都更优：

UPDATE ct
SET ct.colValue = dt.value
FROM #ColumnTable ct
JOIN (
    SELECT cid, columnName, value
    FROM #DataTable
    UNPIVOT (
        value FOR columnName IN (col1, col2, col3, col4, col5)
    ) AS unpvt
) dt ON ct.cid = dt.cid AND ct.columnName = dt.columnName;

原理：UNPIVOT把#DataTable的多列数据转成cid+columnName+value的行结构，与#ColumnTable的主键完全匹配，一次关联即可完成所有更新。

方案2：动态UNPIVOT（动态列场景） #

如果#DataTable的列是动态变化的，可通过系统视图自动获取列名，生成动态SQL执行：

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

-- 从临时表系统视图获取除cid外的所有列名
SELECT @columns = STRING_AGG(QUOTENAME(column_name), ', ')
FROM tempdb.sys.columns
WHERE object_id = OBJECT_ID('tempdb..#DataTable')
AND column_name != 'cid';

-- 生成并执行动态更新SQL
SET @sql = N'
UPDATE ct
SET ct.colValue = dt.value
FROM #ColumnTable ct
JOIN (
    SELECT cid, columnName, value
    FROM #DataTable
    UNPIVOT (
        value FOR columnName IN (' + @columns + N')
    ) AS unpvt
) dt ON ct.cid = dt.cid AND ct.columnName = dt.columnName;';

EXEC sp_executesql @sql;

优势：无需硬编码列名，新增列时自动适配，仅需一次关联更新，比逐列更新的动态SQL减少多次IO开销。

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