column name which contains max Value in sql server table.

Swagat P

8y1.1k

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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

M Rahman8y ago
Accepted 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
Swagat P8y ago
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 Sontakke8y ago
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
Nitin Sontakke8y ago
This is the duplicate of:

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