title: Case when SQL Server 2008
body: <p>I have a single table with no relations.  When the field countyName is a zero length string I need <code>Statewide</code> to be in the result set.  Otherwise, I need then value of the field to stay as it is, e.g.: <img src="https://i.stack.imgur.com/3q5aZ.jpg" alt="enter image description here"> notice how in the second column there is 'Statewide' and 'countyName'.  countyName should be the original value actually stored in the database.</p>
  
  <pre><code>`countyName`  `address`
                 blah blah
  Jackson        blah blah
  </code></pre>
  
  <p>needs to be (first rows on both examples are column names)</p>
  
  <pre><code>countyName     address
  Statewide      blah blah
  Jackson        blah blah
  </code></pre>
  
  <p>Here's what I tried, you can ignore the rest of the fields in this example</p>
  
  <pre><code>select case servicetype
         when 'cr' then 'Community Resource'
         when 'ed' then 'Education'
         when 'fb' then 'Faith-based'
         when 'me' then 'Medical Equipment'
         when 'hc' then 'Health Care'
         else 'Other' 
         end as serviceType
  
         ,case countyName
         when '' then 'Statewide'
         else 'countyname' end
  
         ,name
         ,physicaladdress
         ,city
         ,statelocation
         ,zip
         ,phone
         ,website
         from main
  
        order by countyName, servicetype, name
  </code></pre>