title: How to use CASE statement along with a Sub Query in SQL Server 2008
body: <p>I have the following query.</p>
  
  <pre><code>SELECT * 
  FROM 
      (SELECT 
          Users.ccMailName as [User Name], 
          (CASE WHEN UserRoleAssigns.Team_ID &gt; 0 THEN (select Team from Teams where team_ID = UserRoleAssigns.team_id) Else "All"  END) as Team, 
          Users.User_WWID [WWID], 
          Users.Created_Date, 
          Role, 
          COUNT(Role) as HasRole
       FROM  
          UserRoles 
       RIGHT OUTER JOIN 
          UserRoleAssigns ON UserRoles.Role_ID = UserRoleAssigns.Role_ID 
       RIGHT OUTER JOIN 
          Users ON UserRoleAssigns.User_WWID = Users.User_WWID
       GROUP BY 
          Users.ccMailName, Users.Created_Date, Users.User_WWID, Role, UserRoleAssigns.Team_ID
  ) as Tbl1
    PIVOT (
      SUM(HasRole)
      FOR [Role] IN (
        ' + @PivotColumnHeaders + '
      )
    ) AS pt
  ORDER BY Created_Date desc '
  </code></pre>
  
  <p>When I run it I am getting the following error: </p>
  
  <blockquote>
    <p>Invalid column name 'All'. </p>
  </blockquote>
  
  <p>The query works fine without the case statement with just the sub query alone. Essentially, first I want to check whether <code>UserRoleAssigns.Team_ID &gt; 0</code>. If so, then get data from the subquery. </p>
  
  <p>Otherwise default the value to "All". </p>
  
  <p>Thanks</p>