How can I SELECT multiple columns within a CASE WHEN on SQL Server?

Question

I have searched this site extensively but cannot find a solution.

Here is the example of my query:

SELECT     ActivityID,     Hours = (CASE                  WHEN ActivityTypeID <> 2 THEN                      FieldName = (Some Aggregate Sub Query),                      FieldName2 = (Some other aggregate sub query)                 WHEN ActivityTypeID = 2 THEN                      FieldName = (Some Aggregate Sub Query with diff result),                      FieldName2 = (Some Other Aggregate Sub Query with diff result)            END) 

obviously I'm leaving out a lot of the query, I just wanted to see if it's possible.

I know I probably could just do the "CASE" twice but figured I would ask...

Answer 1

The problem is that the CASE statement won't work in the way you're trying to use it. You can only use it to switch the value of one field in a query. If I understand what you're trying to do, you might need this:

SELECT     ActivityID,    FieldName = CASE                    WHEN ActivityTypeID <> 2 THEN                       (Some Aggregate Sub Query)                   ELSE                      (Some Aggregate Sub Query with diff result)                END,    FieldName2 = CASE                   WHEN ActivityTypeID <> 2 THEN                       (Some Aggregate Sub Query)                   ELSE                      (Some Aggregate Sub Query with diff result)                END 

Answer 2

No, CASE is a function, and can only return a single value. I think you are going to have to duplicate your CASE logic.

The other option would be to wrap the whole query with an IF and have two separate queries to return results. Without seeing the rest of the query, it's hard to say if that would work for you.