SQL Server: add a column and get other column names as its value

Question

Here is a sample table

    DECLARE @t TABLE(a INT,b INT,c INT);
    INSERT @t VALUES(1,2,3),(9,8,7),(4,6,5);
    SELECT *
    ,      (   SELECT  MAX(val) 
               FROM    (VALUES (a)
                   ,   (b)
                   ,   (c)
               ) AS value(val)
           ) AS MaxVal 
    FROM @t;

Result:

A   B   C   MAX
---------------
1   2   3   3
9   8   7   9
4   6   5   6

I would like to add a column Max_cols which will have other column names as its values. column names are respective to the values that column 'MAX' has in it ...(hope this makes sense).

The result should look like this.

A   B   C   MAX   Max_cols
--------------------------
1   2   3     3       C
9   8   7     9       A
4   6   5     6       B

Answer 1

You can't really do this dynamically (at least not with table variables); one solution would be a CASE expression that explicitly lists each potential column name. You haven't explained how you want to handle ties, though...

;WITH x AS (SELECT *,
 (
   SELECT  MAX(val) 
   FROM    (VALUES (a)
           ,   (b)
           ,   (c)
           ) AS value(val)
    ) AS MaxVal 
    FROM @t
 ) SELECT *, Max_cols = CASE MaxVal 
 WHEN a THEN 'A' WHEN b THEN 'B' WHEN c THEN 'C' END
 FROM x;