使用cte和接头，如下所示：

with cte as (select user_id, column1 as col, 1 [group] 
from #temp
union all
select user_id, column2, 2 [group]
from #temp
union all
select user_id, column3, 3 [group]
from #temp
union all
select user_id, column4, 4 [group]
from #temp
union all
select user_id, column5, 5 [group]
from #temp) 

select a.user_id, 'column'+cast([group] as varchar(max)) highest_val from cte a join
(select user_id, max(col) col from cte
group by user_id )b
on a.user_id=b.user_id and a.col=b.col

假设没有值 NULL ，您可以使用 case 表达式：

select t.*,
       (case when column1 = greatest(column1, column2, column3, column4, column5) then 'column1'
             when column2 = greatest(column1, column2, column3, column4, column5) then 'column2'
             when column3 = greatest(column1, column2, column3, column4, column5) then 'column3'
             when column4 = greatest(column1, column2, column3, column4, column5) then 'column4'
             when column5 = greatest(column1, column2, column3, column4, column5) then 'column5'
      end) as greatest_column             
from t;

你也可以这样说：

select t.*,
       (case greatest(column1, column2, column3, column4, column5)
             when column1 then 'column1'
             when column2 then 'column2'
             when column3 then 'column3'
             when column4 then 'column4'
             when column5 then 'column5'
      end) as greatest_column             
from t;

使用 CASE 表达式来获取相应的列名。

INSERT INTO table2 (user_id, highest_val)
SELECT user_id,
    CASE GREATEST(column1, column2, column3, column4, column5)
        WHEN column1 THEN 'column1'
        WHEN column2 THEN 'column2'
        WHEN column3 THEN 'column3'
        WHEN column4 THEN 'column4'
        ELSE 'column5'
    END AS highest_val
FROM table1