title: CASE in a SELECT statement and multiple expression at THEN
body: <p><a href="http://msdn.microsoft.com/en-us/library/ms181765.aspx" rel="nofollow noreferrer">CASE</a>  syntax.</p>
  
  <pre><code>SELECT 
     ID,
     CASE 
        WHEN ID != 2 THEN 'Others'
        ELSE ColorName
     END AS Name
  FROM ColorTable
  </code></pre>
  
  <p>This gives me the following result:</p>
  
  <pre><code>ID - Name 
  1 - Others
  2 - Red
  3 - Others
  4 - Others
  </code></pre>
  
  <p>My requirement is to increment 'Others' by a value by one:</p>
  
  <pre><code>ID - Name 
  1 - (1)Others
  2 - Red
  3 - (2)Others
  4 - (3)Others
  </code></pre>
  
  <p>I have tried the following code but it failed:</p>
  
  <pre><code>DECLARE @Count INT = 1
  SELECT 
     ID,
     CASE 
        WHEN ID != 2 THEN '(' + CONVERT(varchar(10), @Count)   + ')Others' SET @Count = @Count + 1
        ELSE ColorName
     END AS Name  
  FROM ColorTable
  </code></pre>
  
  <p><strong>Any suggestions? How will it give such result?</strong></p>