column name which contains max Value in sql server table.

Question

I have a color table with columns Code,Red ,Black, Green with values Cd1,100 ,200,300 . I want a sql query which will show the column name having highest value. Expected Output - Green 300

M Rahman · Answer

Hi, Dear Friend column name which contains max Value in sql server table CREATE TABLE [dbo].[test]( [id] [int] NOT NULL, [sal1] [int] NULL, [sal2] [int] NULL, [sal3] [int] NULL ) ON [PRIMARY] id sal1 sal2 sal3 1 200 4000 900 Below Code : declare @s1 int set @s1=(SELECT MAX(lvalue) AS bigeestno FROM test UNPIVOT (lvalue FOR Val IN (sal1, sal2, sal3)) AS Number) select (case when sal1=@s1 then 'sal1' else '' end), (case when sal2=@s1 then 'sal2' else '' end), (case when sal3=@s1 then 'sal3' else '' end) from test Thanks

Nitin Sontakke · Answer

In that case, following is much simpler: DECLARE @T AS TABLE ( pk integer PRIMARY KEY , col1 integer NULL , col2 integer NULL , col3 integer NULL , col4 integer NULL ); INSERT @T VALUES (1, 4, 3, 2, 1), (2, 5, NULL , 7, 8); SELECT MAX (f.x) AS Greatest , case MAX (f.x) when col1 then 'Col1' when col2 then 'Col2' when col3 then 'Col3' when col4 then 'Col4' end ColumnName FROM @T AS t CROSS APPLY ( VALUES (col1), (col2), (col3), (col4)) AS f (x) GROUP BY t.pk, t.col1, t.col2, t.col3, t.col4 Built on top of the code copied from the following link: https://www.sqlservercentral.com/Forums/Dialogs/PostDetails.aspx?PostID=1348803

Swagat P · Answer

Thanks all for your answers Here is the query which will give the expected output for my question. SELECT MAX(f.x) AS Greatest ,case MAX(f.x) when Red then 'Red' when Black then 'Black' when Green then 'Green ' end ColumnName FROM [your table name] as t CROSS APPLY (VALUES (Red ), (Black ), (Green ) AS f (x) where Code=Cd1 GROUP BY t.pk, t.Red, t.Black , t.Green

Nitin Sontakke · Answer

This is the duplicate of: https://www.c-sharpcorner.com//forums/how-to-get-column-name-which-having-max-value-in-sql-server