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...

Thanks!

Answer 1

Man, you got one complicated ordah.

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.

Answer 3

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