title: What's the best way to select the minimum value from several columns?
body: <p>Given the following table in SQL Server 2005:</p>
  
  <pre><code>ID   Col1   Col2   Col3
  --   ----   ----   ----
  1       3     34     76  
  2      32    976     24
  3       7    235      3
  4     245      1    792
  </code></pre>
  
  <p>What is the best way to write the query that yields the following result (i.e. one that yields the final column - a column containing the minium values out of Col1, Col2, and Col 3 <strong>for each row</strong>)?</p>
  
  <pre><code>ID   Col1   Col2   Col3  TheMin
  --   ----   ----   ----  ------
  1       3     34     76       3
  2      32    976     24      24
  3       7    235      3       3
  4     245      1    792       1
  </code></pre>
  
  <p><strong><em>UPDATE:</em></strong></p>
  
  <p>For clarification (as I have said in the coments) in the real scenario the database is <strong>properly normalized</strong>. These "array" columns are not in an actual table but are in a result set that is required in a report. And the new requirement is that the report also needs this MinValue column. I can't change the underlying result set and therefore I was looking to T-SQL for a handy "get out of jail card".</p>
  
  <p>I tried the CASE approach mentioned below and it works, although it is a bit cumbersome. It is also more complicated than stated in the answers because you need to cater for the fact that there are two min values in the same row.</p>
  
  <p>Anyway, I thought I'd post my current solution which, given my constraints, works pretty well. It uses the UNPIVOT operator:</p>
  
  <pre><code>with cte (ID, Col1, Col2, Col3)
  as
  (
      select ID, Col1, Col2, Col3
      from TestTable
  )
  select cte.ID, Col1, Col2, Col3, TheMin from cte
  join
  (
      select
          ID, min(Amount) as TheMin
      from 
          cte 
          UNPIVOT (Amount for AmountCol in (Col1, Col2, Col3)) as unpvt
      group by ID
  ) as minValues
  on cte.ID = minValues.ID
  </code></pre>
  
  <p>I'll say upfront that I don't expect this to offer the best performance, but given the circumstances (I can't redesign all the queries just for the new MinValue column requirement), it is a pretty elegant "get out of jail card".</p>