T-SQL: Get column name with highest value

Question

Let's say I have a table in SQL Server that has the following columns:

COMID, HLR1, HLR2, HLR3....HLR20, HLR_NODATA

There are over a million rows in this table. I need to get the name of the column with the highest value per row and display them like this:

COMID COLUMN_NAME VALUE

Appreciate any suggestions.

Answer 1

Try one of alternatives:

select COMID, c as COLUMN_NAME, v as [VALUE]
from
(
	select COMID, c, v, row_number() over (partition by COMID order by v desc) as i
	from ATable
	unpivot
	(
	 v for c in (HLR1, HLR2, HLR3, HLR4, HLR5, HLR6, HLR7, HLR8, HLR9, HLR10, HLR11, HLR12, HLR13, HLR14, HLR15, HLR16, HLR17, HLR18, HLR19, HLR20, HLR_NODATA)
	) u
) d
where i = 1

Answer 2

Hi @deskcheck1-0579,

A minimal reproducible example is not provided. Shooting from the hip.

It works in a generic way without mentioning column names explicitly.

-- DDL and sample data population, start
DECLARE @tbl TABLE (COMID INT IDENTITY PRIMARY KEY, HLR1 INT, HLR2 INT, HLR3 INT);
INSERT @tbl (HLR1, HLR2, HLR3) VALUES
(1,2,30),
(9,0,2);
-- DDL and sample data population, end

SELECT t.*
	, column_name = x.value('local-name((/root/*[local-name()!="COMID"][xs:decimal(text()[1]) = xs:decimal(max(/root/*[local-name()!="COMID"]))])[1])', 'SYSNAME')
	, max_value = x.value('max(/root/*[local-name()!="COMID"]/text())', 'INT')
FROM @tbl AS t
CROSS APPLY (SELECT t.* FOR XML PATH(''), TYPE, ROOT('root')) AS t1(x);

Output

Answer 3

--with Yitzhak Khabinsky's sample 
-- DDL and sample data population, start
DECLARE @tbl TABLE (COMID INT IDENTITY PRIMARY KEY, HLR1 INT, HLR2 INT, HLR3 INT);
INSERT @tbl (HLR1, HLR2, HLR3) VALUES
(1,2,30),
(9,0,2);
 
--Option unoivot using cross apply 
;with mycte as (select *
,row_number() over (partition by COMID order by max_value desc) as i
FROM @tbl AS t
CROSS APPLY (values('HLR1', HLR1), ('HLR2', HLR2),('HLR3', HLR3)) d (column_name,max_value)
)
select COMID,HLR1,HLR2,HLR3,column_name,max_value
 from   mycte m 
where i=1
--Option JSON 
 ;with mycte   (TheKey, TheValue) as (
SELECT [KEY], Value from OpenJson((
select * from @tbl  FOR JSON AUTO ))
WHERE type = 5)
  
 ,mycte2 as (  
 SELECT
 --columns not in unpivot list
 JSON_VALUE(src.TheValue,'$.COMID') COMID
 ,JSON_VALUE(src.TheValue,'$.HLR1') HLR1
  ,JSON_VALUE(src.TheValue,'$.HLR2') HLR2
   ,JSON_VALUE(src.TheValue,'$.HLR3') HLR3
 
 ,unpvt.[Key] column_name, unpvt.Value  max_value
 ,row_number() over (partition by JSON_VALUE(src.TheValue,'$.COMID')
 order by unpvt.Value desc) as rn
 FROM mycte AS src
 CROSS APPLY OpenJson(src.TheValue) AS unpvt  
 WHERE  unpvt.[Key] not in ('COMID')
 )

 Select COMID,HLR1,HLR2,HLR3,column_name,max_value 
   from mycte2
 where rn=1