title: select column name from max query
body: <p>I have a query that goes something like this : </p>
  
  <pre><code> ;WITH t as
  (
  select 1 as RowNumber, 1 as ObjectID, 10 as [Col1],  20 as [Col2],  20 as [Col3],  20 as [Col4] UNION ALL
  select 2 as RowNumber, 2 as ObjectID, 20 as [Col1],  30 as [Col2],  40 as [Col3],  50 as [Col4]
  )
  SELECT  RowNumber, ObjectID,
          (
          SELECT  MAX(Amount)
          FROM    (
                  SELECT  [Col1] AS Amount
                  UNION ALL
                  SELECT  [Col2]
                  UNION ALL
                  SELECT  [Col3]
                  UNION ALL
                  SELECT  [Col4]
                  ) d
         WHERE   Amount &gt; 0
          )
  FROM    t
  </code></pre>
  
  <p>The query works fine, but I want to know is where the Max(Amount) comes from.</p>
  
  <p>So in my result set, on top of having (RowNumber, ObjectId, Amount) I want the name of the column (Col1, Col2, Col3, Col4) as a String.</p>
  
  <p>Is there any way to do that?</p>
  
  <p><strong>EDIT</strong>
  Question from the comments : If two columns have the same max, it could be either one? Yes, it could be either one. Any column name will do as long as I know where it could be coming from.</p>
  
  <p>Using SQL Server 2008</p>