SQL Server如何按列值返回列名与值并更新指定列？

哈哈，这种要处理上百列的场景，静态SQL确实顶不住，必须上动态SQL才能解放双手！既然你已经用了CROSS APPLY，那咱们就顺着这个思路扩展，实现动态提取每行前5个最高值的列名和对应值，再更新到目标列里。

核心思路拆解 #

1. 动态识别目标列：先从系统视图里自动抓取所有需要比较的颜色值列（排除掉first_color、first_value这类结果列），不用手动写100多列。
2. 行转列打散数据：用CROSS APPLY把每行的多列颜色值转成（列名, 值）的行格式，方便排序取前5。
3. 排名+转列还原：给每行的颜色值按降序排名，再把前5名的列名和值转回到列格式，最后关联原表更新。

具体代码实现（SQL Server为例） #

假设你的表叫ColorData，主键是RecordId，结果列是first_color、first_value一直到fifth_color、fifth_value，直接用下面的动态SQL就能搞定：

DECLARE @TargetColumns NVARCHAR(MAX);
DECLARE @UnpivotValues NVARCHAR(MAX);

-- 第一步：动态获取所有需要比较的颜色列（排除结果列）
SELECT 
    @TargetColumns = STRING_AGG(QUOTENAME(name), ', '),
    @UnpivotValues = STRING_AGG(N'(N''' + name + N''', cd.' + QUOTENAME(name) + N')', ', ')
FROM sys.columns
WHERE object_id = OBJECT_ID('ColorData')
AND name NOT IN (
    'RecordId', 'first_color', 'first_value', 
    'second_color', 'second_value', 'third_color', 
    'third_value', 'fourth_color', 'fourth_value', 
    'fifth_color', 'fifth_value'
);

-- 第二步：构造动态更新SQL
DECLARE @DynamicSQL NVARCHAR(MAX) = N'
WITH RankedColors AS (
    SELECT 
        cd.RecordId,
        ColorName = ca.ColorName,
        ColorValue = ca.ColorValue,
        -- 按值降序排名，相同值会随机取一个，要保留并列用RANK()/DENSE_RANK()
        ColorRank = ROW_NUMBER() OVER (PARTITION BY cd.RecordId ORDER BY ca.ColorValue DESC)
    FROM ColorData cd
    CROSS APPLY (
        VALUES ' + @UnpivotValues + N'
    ) ca(ColorName, ColorValue)
),
PivotedResults AS (
    SELECT
        RecordId,
        first_color = MAX(CASE WHEN ColorRank = 1 THEN ColorName END),
        first_value = MAX(CASE WHEN ColorRank = 1 THEN ColorValue END),
        second_color = MAX(CASE WHEN ColorRank = 2 THEN ColorName END),
        second_value = MAX(CASE WHEN ColorRank = 2 THEN ColorValue END),
        third_color = MAX(CASE WHEN ColorRank = 3 THEN ColorName END),
        third_value = MAX(CASE WHEN ColorRank = 3 THEN ColorValue END),
        fourth_color = MAX(CASE WHEN ColorRank = 4 THEN ColorName END),
        fourth_value = MAX(CASE WHEN ColorRank = 4 THEN ColorValue END),
        fifth_color = MAX(CASE WHEN ColorRank = 5 THEN ColorName END),
        fifth_value = MAX(CASE WHEN ColorRank = 5 THEN ColorValue END)
    FROM RankedColors
    GROUP BY RecordId
)
UPDATE cd
SET
    cd.first_color = pr.first_color,
    cd.first_value = pr.first_value,
    cd.second_color = pr.second_color,
    cd.second_value = pr.second_value,
    cd.third_color = pr.third_color,
    cd.third_value = pr.third_value,
    cd.fourth_color = pr.fourth_color,
    cd.fourth_value = pr.fourth_value,
    cd.fifth_color = pr.fifth_color,
    cd.fifth_value = pr.fifth_value
FROM ColorData cd
INNER JOIN PivotedResults pr ON cd.RecordId = pr.RecordId;
';

-- 执行动态SQL
EXEC sp_executesql @DynamicSQL;

关键注意事项 #

• 兼容低版本SQL Server：如果用的是2017之前的版本，STRING_AGG不能用，得换成FOR XML PATH来拼接列名，比如：

  -- 替换STRING_AGG的列名拼接
  SELECT @TargetColumns = STUFF((
      SELECT ', ' + QUOTENAME(name)
      FROM sys.columns
      WHERE object_id = OBJECT_ID('ColorData') AND name NOT IN (...)
      FOR XML PATH(''), TYPE
  ).value('.', 'NVARCHAR(MAX)'), 1, 2, '');
  

• 并列值处理：如果想保留值相同的并列项，把ROW_NUMBER()换成DENSE_RANK()，但要注意可能会超过5个，根据你的需求调整。
• 主键依赖：必须有唯一标识列（比如RecordId）来关联原表和临时结果集，不然没法准确更新每行的数据。

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